CODEWITHSUNDEEP
postgresqlsql-like
Matt
Matt

Reputation: 31

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 []

Upvotes: 0

Views: 2451

Answers (1)

user330315
user330315

Reputation:

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 '#'

Upvotes: 3

Related Questions