I have a SQL table that contains prefixes of strings that I would like to match against longer strings. Suppose the column containing the prefix is called prefix.
I now want to do a simple search for all rows that prefix a longer string. My first thought is to use a query that is something like this:
SELECT prefix FROM Prefixes WHERE 'long string' LIKE prefix%
The point in question is how I get the WHERE clause right. How can I tell SQL to use the value in column prefix as a prefix for my argument 'long string'?
And can I also tell SQL to not only use the column ‘prefix’ as a prefix for 'long string', but instead check whether 'long string' contains prefix anywhere within itself?
Something like this:
SELECT prefix FROM Prefixes WHERE 'long string' LIKE %prefix%
If vendor-specific things play a role here, I am using JDBC to run the queries and my database is either H2 or PostgreSQL.
>Solution :
Use concatenation to generate the string that contains the value of the column prefix with the wildcard(s):
SELECT prefix FROM Prefixes WHERE 'long string' LIKE prefix || '%'
If you want to check whether ‘long string’ contains prefix anywhere within itself:
SELECT prefix FROM Prefixes WHERE 'long string' LIKE '%' || prefix || '%'
You could also use the function CONCAT():
SELECT prefix FROM Prefixes WHERE 'long string' LIKE CONCAT(prefix, '%')
or:
SELECT prefix FROM Prefixes WHERE 'long string' LIKE CONCAT('%', prefix, '%')
See a simplified demo.