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
Add a comment
|
2 Answers
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.
answered Oct 11, 2017 at 2:09
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
Comments
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
