How to regex_replace in SQL?

Question

I have a column with data as:

D:\SomeFolder\File1.jpg
D:\SomeFolder\File2.jpg
D:\SomeFolder\File3.jpg

How do I replace the characters using a SQL query such that column is updated like this:

E:\DifferentFolder\File1.jpg
E:\DifferentFolder\File2.jpg
E:\DifferentFolder\File3.jpg

Do you specifically want to target only the folder named SomeFolder ? — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 8, 2022 at 7:53

Simeon Pilgrim · Accepted Answer · 2022-03-08 07:58:10Z

1

If you wan to match the first word use:

SELECT column1
    ,REGEXP_REPLACE(column1, '(^[^\\\\]+\\\\)([^\\\\]+)\\\\', '\\1OtherWord\\\\', 1, 1, 'e')
FROM VALUES 
    ('D:\\SomeFolder\\File1.jpg'),
    ('D:\\SomeFolder\\File2.jpg'),
    ('D:\\SAMEFolder\\File2.jpg'),
    ('D:\\SomeFolder\\File3.jpg');

COLUMN1	REGEXP_REPLACE(COLUMN1, '(^[^\\]+\\)([^\\]+)\\', '\1OTHERWORD\\', 1, 1, 'E')
D:\SomeFolder\File1.jpg	D:\OtherWord\File1.jpg
D:\SomeFolder\File2.jpg	D:\OtherWord\File2.jpg
D:\SAMEFolder\File2.jpg	D:\OtherWord\File2.jpg
D:\SomeFolder\File3.jpg	D:\OtherWord\File3.jpg

answered Mar 8, 2022 at 7:58

Simeon Pilgrim

26.7k3 gold badges37 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D-Shih · Accepted Answer · 2022-03-08 07:50:17Z

1

You can try to use replace function.

select replace(val, 'SomeFolder','DifferentFolder') from T

answered Mar 8, 2022 at 7:50

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Comments

Knox · Accepted Answer · 2022-03-08 07:51:54Z

1

In snowflake, you can use

REPLACE( <subject> , <pattern> [ , <replacement> ] )

So in your case

REPLACE(<column_name>, 'SomeFolder', 'DifferentFolder')

should do the trick.

https://docs.snowflake.com/en/sql-reference/functions/replace.html

answered Mar 8, 2022 at 7:51

Knox

214 bronze badges

Collectives™ on Stack Overflow

How to regex_replace in SQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related