Substring in Postgresql

Question

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:

5026 S CRENSHAW BLVD - Should be CRENSHAW
2635 WHITTIER BLVD - Should be WHITTIER
308 WESTWOOD PLZ # 1390L - Should Be WESTWOOD
1111 WILSHIRE BLVD - Should be WILSHIRE

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! :)

postgresql.org/docs/11/functions-matching.html

wildplasser
– wildplasser

2021-04-19 19:29:35 +00:00
Commented Apr 19, 2021 at 19:29 — wildplasser
– wildplasser, Commented Apr 19, 2021 at 19:29

Marko · Accepted Answer · 2021-04-19 21:19:44Z

2

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.

edited Apr 19, 2021 at 21:19

answered Apr 19, 2021 at 19:31

Marko

1,00012 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Substring in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related