Search code examples
sqlselectdb2where-clausesign

SQL - how to check in WHERE clause if some sign (#) exists at least two times in string (i.e. AB#CDEF#)?

I have column with string values. I would like to have Select statement which will return all rows where sign # is present two or more times?

For example:

COL1  COL2
1     AB#CDE#
2     AB#
3     AB#CDE#FG#IJ#

If I do 

SELECT * FROM TABLE WHERE COL2 LIKE "%#%"

it will return all three rows but I need 1st and 3rd.

Thank you,

Solution

  • SELECT * FROM TABLE WHERE COL2 LIKE "%#%#%"

    As long as there's at least 2 instances of "#" then this will catch it.