CODEWITHSUNDEEP
sqlpostgresql
O James
O James

Reputation: 319

Can you explain the following SQL (postgres) code?

I asked SO for a query to find all rows in a table with a 'code' entry that is a substring of the search string, with the added condition that it appears at the end of the search string.

So a query for '12345' should return '2345', '345', '45', and '5'.

I was given this answer, which works. I have read through the documentation but still don't understand the query. Can someone please explain

SELECT * from yourtable
where '12345' like '%' || Code

Upvotes: 0

Views: 80

Answers (2)

LukStorms
LukStorms

Reputation: 29677

Normally a LIKE is used in the opposite way.

For example:

SELECT * FROM SomeTable
WHERE SomeColumn LIKE '%xxx%'

So you check if the column matches against a fixed string with a pattern.

But the clever thing about that answer was it did the opposite.

It checks a fixed string again a pattern that's created from a column.

SELECT * FROM SomeTable
WHERE 'bar456' LIKE '%' || SomeColumn;

In this example, if "SomeColumn" contains the value "56"?
Then '%' || SomeColumn forms the string '%56'

So 'bar456' is like '%56', since it ends with '56'
And 'bar456' is also like '%ar456'

Upvotes: 3

paulsm4
paulsm4

Reputation: 121881

There are two relevant documentation links you need:

  1. PostgreSQL Pattern Matching: '12345' like '%'

  2. PostgreSQL CONCATENATE(||) Operator: <match> || Code

The SQL means:

  1. Fetch all columns from the table
  2. IF column "code" is equal to <match> + <value of "code" column>

Upvotes: 1

Related Questions