CODEWITHSUNDEEP
sqlpostgresqlsubstring
Lorena Gonzales Saar
Lorena Gonzales Saar

Reputation: 65

Substring in Postgresql

I have a quick question. I'm studying some SQL exercises, and one of them, I need to remove the number from a street name.

For example:

Then, the answer to treat the address above was like this:

substring(facility_address FROM '[\d]+\s?\w?\s([\w]+)\s?')

I would like to understand how the substring works, what does it means de [\D] etc.

Could someone explain? Thank you very much indeed! :)

Upvotes: 0

Views: 77

Answers (1)

Marko
Marko

Reputation: 988

You should read up on regular expressions.

'[\d]+\s?\w?\s([\w]+)\s?' is a regular expression.

I'll try to break it down:

First of all, these are quantifiers:

+ means one or more

? means one or none

And now for the regular expression:

[\d]+ matches one or more digits. I think the square brackets are actually not necessary here.

\s? optionally matches a whitespace character (optionally meaning it may or may not be there)

\w? optionally matches a word character

\s matches a whitespace character, but this time it's not optional since there is no ? at the end

([\w]+) matches one or more word characters. Notice the parenthesis which denote a so called 'capture group`. Everything within the parenthesis is actually returned by the substring function.

Upvotes: 2

Related Questions