Snowflake SQL: regex_replace for CamelCase strings

Question

Hi I need to transform the following string as follows:

Before: 6621 Lake Valley DrMemphis, TN 38141

After: 6621 Lake Valley Dr

Additional ways the data comes in: 143 Evergreen Forest Court (this one is fine as is)

Need to extract address only for these as well:

326 Hambrick Park Fayetteville, GA 30215
RE: Owner's Policy - 112 Shagbark Ln Mooresville, NC 28115
RE: Owner's Policy - 540 Clearbrook Dr Covington, GA 30016
Closed 9/1/21 4421 Home Stakes Dr Parkton, NC 28371
RP 9/16- 352 Hampton St Elloree, SC 29047
RP: 9/15- 124 Lake Grove Rd Simpsonville, SC 29681
FHA 3/2/22- 6083 Holiday Blvd Forest Park, GA 30297
RD 10/1/21 Roxanne Sellers- 311 Woodbrook Ln Marietta, GA 30068
4104 Flat Trl- Ricardo Reeder

How can I accomplish this using Snowflake SQL? I'm assuming regex_replace is in order? Can anybody help me out?

Can you add more examples of the transformation you need?

Felipe Hoffa
– Felipe Hoffa

2022-01-28 06:59:19 +00:00
Commented Jan 28, 2022 at 6:59 — Felipe Hoffa
– Felipe Hoffa, Commented Jan 28, 2022 at 6:59

Felipe Hoffa · Accepted Answer · 2022-01-28 07:04:13Z

1

If you only give us one example, we can only solve for that example:

select regexp_replace(x, '(.*[a-z])([A-Z].*)', '\\1')
from (select '6621 Lake Valley DrMemphis, TN 38141' x)

If you need help with a more general case, please start a new question with more sample inputs and outputs.

answered Jan 28, 2022 at 7:04

Felipe Hoffa

59.8k23 gold badges185 silver badges365 bronze badges

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

2 Comments

cyahahn Over a year ago

Good call, Felipe! Here's some other examples of how the inputs look: 1306 Eddie Craig Ct McDonough, GA 30252 143 Evergreen Forest Court (this one is fine as is). I made the edits above

cyahahn Over a year ago

Ok your solution helped with one of my issues, but doesn't work for the strings that look like this: 7401 Bosson St SW Concord, NC 28025

Tim Biegeleisen · Accepted Answer · 2022-01-28 06:10:31Z

0

We can try using REGEXP_REPLACE with the help of a capture group:

WITH yourTable AS (
    SELECT '6621 Lake Valley DrMemphis, TN 38141' AS address
)

SELECT REGEXP_REPLACE(address, '(\d+( [A-Z][a-z]+)*).*', '\1') AS address_out
FROM yourTable;

Demo

The above working demo is for Postgres, not Snowflake. You might have to use $1 instead of \1 for the first capture group, but the rest should run unaltered.

edited Jan 28, 2022 at 6:10

answered Jan 28, 2022 at 5:54

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

3 Comments

cyahahn Over a year ago

That resulted in: Invalid regular expression: '(d+(?: [A-Z][a-z]+)*).*', no argument for repetition operator: ? I removed the ? and it didn't result in my desired output (just gave me my original string back)

Tim Biegeleisen Over a year ago

Try the updated version.

cyahahn Over a year ago

Still gave me my original string back. Also tried it with $1 and same thing :/

Collectives™ on Stack Overflow

Snowflake SQL: regex_replace for CamelCase strings

2 Answers 2

2 Comments

Demo

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related