Visited Cities Visited Cities - 1 year ago 73
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:


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 Source

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

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.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download