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
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'
There are two relevant documentation links you need:
PostgreSQL Pattern Matching: '12345' like '%'
PostgreSQL CONCATENATE(||) Operator: <match> || Code
The SQL means:
<match> + <value of "code" column>
likeoperator.