Like Statement to exclude some in the sequence-CodePudding

I need to run a like statement for 300 criteria and it looks like this;

enter image description here

how do i write a like statement excluding 112, 115, 116 ,117?

CodePudding user response：

You could use LIKE here:

SELECT *
FROM yourTable
WHERE col NOT LIKE '%.112' AND col NOT LIKE '%.115' AND
      col NOT LIKE '%.116' AND col NOT LIKE '%.117';

CodePudding user response：

You can check the string after the last occurence of the dot (in your examples, there is one dot only) with NOT IN. The exact syntax depends on the DB, but the same idea will work on every DB. As example for SQL Server DB, you can do this:

SELECT yourcolumn FROM yourtable
WHERE RIGHT(yourcolumn, CHARINDEX('.',REVERSE(yourcolumn))-1) 
NOT IN (112,115,116,117);

See the working example here: db<>fiddle

In case you are not sure if the substring after the dot is numeric, quote your selection like this:

...NOT IN ('112','115','116','117')

If you need assistance how to convert this to work in another DB, please tag your DB.

CodePudding user response：

You can use the REGEXP function to match the values that you don't need, hence reverse the condition:

SELECT * 
FROM tab
WHERE val NOT REGEXP '11[2567]'

Try it here.