Snowflake : REGEXP replace with uppercase of capture group

Question

I want to replace the very first letter after a comma(,) with uppercase of it in snowflake database. Below given is what I tried, but it did not work.

eg:

Apple,ball,cat --> Apple,Ball,Cat
Bulb,LED,tube --> Bulb,LED,Tube

SELECT  REGEXP_REPLACE('Apple,ball,cat',',(\\\w)',UPPER('\\\1'));

,(\\\w) captures letters after the comma, but UPPER('\\\1') does not convert it to uppercase.

Michael Golos · Accepted Answer · 2021-10-13T12:40:01.273

5

I am not sure if you can use functions inside REGEXP_REPLACE at all.

Please use the built-in INITCAP function

SELECT INITCAP('Apple,ball,cat', ',');

Reference: INITCAP

Or maybe like this:

SELECT LISTAGG(UPPER(LEFT(VALUE, 1)) || SUBSTRING(VALUE, 2, LEN(VALUE)), ',')
  FROM TABLE(SPLIT_TO_TABLE('Apple,ball,cat', ',')) as t(val);

edited Oct 13 '21 at 12:40

answered Oct 13 '21 at 11:54

Michael Golos

1,814
1
6
16

1

Thanks, but I need all letters other than the first letter after comma to remain as it is. INITCAP makes them all lowercase. – Teresa Rosemary Oct 13 '21 at 12:02
I added another possibility in my answer, I know it's not a beautiful solution, but it works. – Michael Golos Oct 13 '21 at 12:41
1

Beat me to it, Michael (+1 btw) ! – Robert Long Oct 13 '21 at 12:42

score 5 · Answer 2 · edited Oct 13 '21 at 14:14

Not "regex", but if you're interested in a Javascript UDF to do what you need...

CREATE OR REPLACE FUNCTION fx_replaceInitOnly(
    input varchar)
returns varchar
language javascript
as '
  //logic from https://www.freecodecamp.org/news/how-to-capitalize-words-in-javascript/
  var words = INPUT.split(",");

  for (let i = 0; i < words.length; i++) {
      words[i] = words[i][0].toUpperCase() + words[i].substr(1);
  }

  output = words.join(",");
  return output;
';

SELECT 
  'Apple,ball,cat,Bulb,LED,Tube' as str, 
  fx_replaceInitOnly(str) as new,
  case WHEN str <> new THEN 'Changed' ELSE 'Same' END as test;

--STR                           NEW                           TEST
--Apple,ball,cat,Bulb,LED,Tube  Apple,Ball,Cat,Bulb,LED,Tube  Changed

Thank you @Michael Golos for the edit! – Rich Murnane Oct 13 '21 at 14:15 — Rich Murnane, Oct 13 '21 at 14:15

Gokhan Atil · Answer 3 · 2021-10-13T12:56:01.160

4

Regexp will not help you to upper your chars, so you may combine split_to_table and initcap:

SELECT LISTAGG( INITCAP(VALUE) ,','  )
FROM TABLE(SPLIT_TO_TABLE('Apple,ball,cat',','));

edited Oct 13 '21 at 12:56

answered Oct 13 '21 at 12:48

Gokhan Atil

9,278
2
11
24

Snowflake : REGEXP replace with uppercase of capture group

3 Answers3