Replace positions with characters in Snowflake SQL-CodePudding

I have several columns where I have to replace positions in strings with underscores.

i.e.

11                11_modified
XX4RDGCG9DR       XX4RDGCG__R

12                12_modified
XX4RDGCG9DRX     XX4RDGCG___X

13                13_modified
XX4RDGCG9DRXY    XX4RDGCG____Y

Notice that I will always just need the first 8-digits, but depending on the column, the number of underscores changes and I only need the last value of a string-value.

11... has 2 underscores at the 9th and 10th position, 12... has 3 underscores at the 9th, 10th, and 11th position, and 13 has 4 underscores at the 9th, 10th, llth, and 12th position.

How would I do this?

CodePudding user response：

Using CONCAT and string manipulation functions:

SELECT col,
   CONCAT(LEFT(col, 8), REPEAT('_', LEN(col)-9), RIGHT(col, 1)) AS modified
FROM tab;

For sample input:

CREATE OR REPLACE TABLE tab
AS
SELECT 'XX4RDGCG9DR' AS col UNION
SELECT 'XX4RDGCG9DRX' UNION
SELECT 'XX4RDGCG9DRXY';

Output:

CodePudding user response：

Another alternative using insert

insert( <base_expr>, <pos>, <len>, <insert_expr> )

set str='XX4RDGCG9DRX';

select insert($str,9,len($str)-9,repeat('_',len($str)-9));

Update:

I noticed both Lukasz's and my solution return unexpected result if len($str) < 9. To fix for that, modify that to the following so that strings that don't qualify remain unchanged. I would honestly use a where clause or a case expression instead

set str='XG9DR';
select insert($str,9,greatest(len($str)-9,0),repeat('_',len($str)-9));