word boundary regex extraction seems working incorrect

[frankiedrake]

Describe what's wrong

I'm trying to extract the first letter from each word in a string
I test my regex in https://regex101.com/ and it works correctly

Does it reproduce on recent release?
I'm unable to try the latest release unfortunately

ClickHouse server version used is 22.8.8.3

How to reproduce

SELECT extractAll('new york is the greatest', '\\b(\\w)') t;
SELECT extractAll('new york is the greatest', '(\\b[a-zA-Z])') t;

Expected behavior
It should return first letter of each word, but I get a String array with each letter in a String instead.
Or maybe I'm using it wrong?

[qoega]

You can use fiddle to check different versions
https://fiddle.clickhouse.com/1c110870-a1da-46a1-aaef-200f9308e73f

[den-crane]

loosely related

SELECT replaceRegexpAll('new york is the greatest', '\\b(\\w)', '[\\0]') AS t
┌─t──────────────────────────────────┐
│ [n]ew [y]ork [i]s [t]he [g]reatest │
└────────────────────────────────────┘

SELECT replaceRegexpAll('new york is the greatest', '\\b(\\w)', concat('xxx', '[\\0]')) AS t
┌─t─────────────────────────────────────────────────┐
│ xxx[n]ew xxx[y]ork xxx[i]s xxx[t]he xxx[g]reatest │
└───────────────────────────────────────────────────┘

SELECT replaceRegexpAll('new york is the greatest', '\\b(\\w)', upper('x\\0x')) AS t
┌─t──────────────────────────────────┐
│ XnXew XyXork XiXs XtXhe XgXreatest │ -- upper is not applied to \0
└────────────────────────────────────┘

[alexey-milovidov]

Confirmed.

Workaround: make sure the regular expression consume the whole word:

milovidov-desktop :) SELECT extractAll('new york is the greatest', '\\b(\\w+)') t;

SELECT extractAll('new york is the greatest', '\\b(\\w+)') AS t

Query id: ae2e1473-e8de-4195-bd65-4280b89f01a6

┌─t────────────────────────────────────┐
│ ['new','york','is','the','greatest'] │
└──────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec. 

Most likely, the issue is related to specifying the substring to match after the previous match.
This is related only to the -All matching functions.

[frankiedrake]

@den-crane Thank you for taking a part! Actually capitalizing the first letter is what I needed to do as a final result.
@alexey-milovidov Can confirm that I'm also unable to apply function to an exctracted regex group (e.g. \\0).
Also tried the replaceRegexpOne and this behaviour repeats.

[alexey-milovidov]

@frankiedrake Could you please show the example with replaceRegexpOne?

[frankiedrake]

@frankiedrake Could you please show the example with replaceRegexpOne?

It is the same as the last one @den-crane mentioned.
I can make a screenshots tomorrow

[alexey-milovidov]

This example is perfectly correct:

SELECT replaceRegexpAll('new york is the greatest', '\\b(\\w)', upper('x\\0x')) AS t
┌─t──────────────────────────────────┐
│ XnXew XyXork XiXs XtXhe XgXreatest │ -- upper is not applied to \0
└────────────────────────────────────┘

upper is not applied to \0

And it should not.
upper('x\\0x') is X\\0X - a substitution pattern that you are using.

And it replaces the first letter of every word because \\w matches a single letter.

[den-crane]

upper('x\\0x') is X\\0X - a substitution pattern that you are using.

I see.

The dumb solution:

SELECT
    extractAll('new york,is the-greatest', '.') AS a,
    arrayMap(i -> if(NOT match(a[i - 1], '\\w'), upper(a[i]), a[i]), arrayEnumerate(a)) AS A,
    arrayStringConcat(A) AS o

Row 1:
──────
a: ['n','e','w',' ','y','o','r','k',',','i','s',' ','t','h','e','-','g','r','e','a','t','e','s','t']
A: ['N','e','w',' ','Y','o','r','k',',','I','s',' ','T','h','e','-','G','r','e','a','t','e','s','t']
o: New York,Is The-Greatest

@frankiedrake check #45217 there is more solutions

[frankiedrake]

This example is perfectly correct:

SELECT replaceRegexpAll('new york is the greatest', '\\b(\\w)', upper('x\\0x')) AS t
┌─t──────────────────────────────────┐
│ XnXew XyXork XiXs XtXhe XgXreatest │ -- upper is not applied to \0
└────────────────────────────────────┘

upper is not applied to \0

And it should not. upper('x\\0x') is X\\0X - a substitution pattern that you are using.

And it replaces the first letter of every word because \\w matches a single letter.

Sad( Sorry, I didn't know this is an expected behaviour