CODEWITHSUNDEEP
sqlpattern-matchingamazon-redshift
Vladimir Stazhilov
Vladimir Stazhilov

Reputation: 1954

LIKE clause on text field with "|" returns no results

I run into a very strange problem when I have a string with | separator in my WHERE clause.

For example the following code return adequate values:

SELECT DISTINCT name
FROM stats
  WHERE name like 'Display |%'
ORDER BY 1 ASC

Display | New         
Display | Rerunning

But when I do this (note the space after |, and with the characters after the space it's the same effect), I get no results for some reason. 

SELECT DISTINCT name
    FROM stats
      WHERE name like 'Display | %'
    ORDER BY 1 ASC

Maybe I should escape "|" ? or why LIKE clause doesn't work?

Upvotes: 0

Views: 48

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

You could use an equivalent regular expression instead to cover all kinds of white space:

...
WHERE name ~ '^Display\s+\|\s*'
...

\s+ .. 1 or more white space characters (class-shorthand for [[:space:]])

\s* .. 0 or more of the same

The | has to be escaped to \| since it has a special meaning in regular expressions (but not in LIKE patterns).

Above links are to the Postgres manual. The manual page for Redshift.

Upvotes: 1

Related Questions