Search code examples
postgresqlsql-like

Searching for an underscore with LIKE

I am using a Postgres server and am having difficulty with XML data types. There seems to be limited information online about Postgres and XML.

I am trying to insert a column of XML data into a temporary table where the Location node contains an underscore, using something similar to LIKE in SQL 

<Country>
    <ID>813</ID>
    <Location>IB_202</Loction>
</Country>

I am using this SQL query to get the data 

SELECT CAST(xpath('/Country/Location/text()', penDetails) as text[])
FROM "dbSystemCheck".tbl_countryitems
WHERE personId = 314
AND CAST(xpath('/Country/Location/text()', penDetails) LIKE '%[_]%'
LIMIT 10

The temporary table is set up the same as the original data table, I am looking to just split the locations.

I have read that underscores need to be escaped hence the []

Solution

  • I have read that underscores need to be escaped hence the []

    The square brackets have no special meaning in a SQL LIKE statement. If you want to escape the underscore you need to specify an ESCAPE character:

    AND CAST(xpath('/Country/Location/text()', penDetails) LIKE '%\_%' ESCAPE '\'

    You can also use a different escape character if you want:

    AND CAST(xpath('/Country/Location/text()', penDetails) LIKE '%#_%' ESCAPE '#'