CODEWITHSUNDEEP
sqlregexoracle-database
Sun Shyne
Sun Shyne

Reputation: 25

Regex to replace space with dash, spacedash, dotspace, dot and apostrophe with empty strings

I'm looking for a better way to write the following SQL statement whether using REGEX or any other way.

SELECT LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SCHOOL_NAME, ' -', ''), '. ', ''), '''', ''), '.', ''), ' ', '-'))

My intent is to apply these transformations:

  1. Replacing Space with Dash

  2. Replacing SpaceDash with Empty String

  3. Replacing Apostrophe with Empty String

  4. Replacing DotSpace with Empty String

  5. Replacing Dot with Empty String

Sample of strings I'm dealing with include double space, apostrophe, dashes:

James valley court ad 

Saint's lee park school 

Harrison lodge - and  hospital

I need these strings to become like:

james-valley-court-ad 

saints-lee-park-school 

harrison-lodge-and-hospital

Upvotes: 0

Views: 1544

Answers (1)

Bohemian
Bohemian

Reputation: 425398

Use regexp_replace:

SELECT REGEXP_REPLACE(REPLACE(LOWER(SCHOOL_NAME), '''', ''), '[ .-]+', '-')

See live demo.

To convert all non-letter characters to hyphens:

SELECT REGEXP_REPLACE(REPLACE(LOWER(SCHOOL_NAME), '''', ''), '[^a-z]+', '-')

See live demo.

Both options produce the following from your input:

james-valley-court-ad
saints-lee-park-school
harrison-lodge-and-hospital

Upvotes: 1

Related Questions