Snowflake : REGEXP replace with uppercase of capture group-CodePudding

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.

CodePudding user response：

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);

CodePudding user response：

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

CodePudding user response：

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',','));