'Regex messy addresses with Snowflake SQL

Hi I need to extract the address only from the following set of strings:

  1. 143 Evergreen Forest Court (this one is fine as is)
  2. 326 Hambrick Park Fayetteville, GA 30215
  3. RE: Owner's Policy - 112 Shagbark Ln Mooresville, NC 28115
  4. RE: Owner's Policy - 540 Clearbrook Dr Covington, GA 30016
  5. Closed 9/1/21 4421 Home Stakes Dr Parkton, NC 28371
  6. RP 9/16- 352 Hampton St Elloree, SC 29047
  7. RP: 9/15- 124 Lake Grove Rd Simpsonville, SC 29681
  8. FHA 3/2/22- 6083 Holiday Blvd Forest Park, GA 30297
  9. RD 10/1/21 Roxanne Sellers- 311 Woodbrook Ln Marietta, GA 30068
  10. 4104 Flat Trl- Ricardo Reeder
  11. 6621 Lake Valley DrMemphis, TN 38141

Ideal Output Example:

Before: 6621 Lake Valley DrMemphis, TN 38141

After: 6621 Lake Valley Dr

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

sqlsnowflake-cloud-data-platform


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions