sqlpostgresqlunicode

How do I search for all rows containing a given unicode character in Postgres


I am after all cells containing the 'LINE SEPARATOR' (U+2028) unicode point. Normally this is encoded as \u+2028 or something similar. However googling how this translates to SQL has given various options none of which seem to work ((N'2028'), set @hexstring = '2028';, vchar(2028))

SELECT * FROM myTable WHERE desc LIKE '% [SOME WAY TO ESCAPE U+2028 ] %'

Solution

  • ANSI SQL answer, use a Unicode character string literal:

    SELECT * FROM myTable WHERE desc LIKE U&'%\2028%'
    

    https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-UESCAPE