Visited Cities Visited Cities - 2 months ago 7
SQL Question

SQL: Query where variable is suffix of field and viceversa

Suppose I have an arbitrary string (for example 'AABCC') and I want to get every row where [col] is equal to this string with any prefix. In that case I would do:

SELECT * FROM table WHERE [col] LIKE '%AABCC';


In my example I would get matches such as 'EEEAABCC', etc.

Now I want to do the inverse thing. I want to get matches where my string is equal to [col] with any prefix:

For example, I would like to get rows where [col] is 'BCC', 'ABCC', etc.

How would I do this?

Answer

It sounds like you want any column that matches the rightmost part of your string for its entire length:

SELECT col FROM tbl
WHERE col LIKE RIGHT('rrrabcd', LENGTH(col))

(Performance for this query is going to be pretty bad since it will have to compute the expression for every row. You may want to rethink your problem if you are doing this often on a lot of data.)

Comments