CODEWITHSUNDEEP
regexpostgresqlcase
efoc
efoc

Reputation: 641

Postgresql: How to use Substring with Regex?

I want to use a substring in a CASE statement that if it returns true then it will return whatever is in the THEN clause, such as:

CASE
WHEN substring(name, '\d\s\d{8}') THEN 'Long Name'
END

Upvotes: 0

Views: 154

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

If you need the substring() you then need to evaluate the string that is returned by the substring function:

case
   when substring('foobar' from '\d\s\d{8}') IS NOT NULL then 'Long Name'
   else 'Short Name'
end

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

Since you only need a boolean result, use a simple regular expression instead of the function substring():

CASE WHEN name ~ '\d\s\d{8}' THEN 'Long Name' END

You can use the same regex pattern. Defaults to NULL if the pattern is not found.

Upvotes: 2

Related Questions