Can I use WHERE with table field on right side of LIKE?-CodePudding

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.

CodePudding user response：

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.