Search code examples
sqlsql-serversql-like

MSSQL WHERE LIKE using Caret I want 1 char but I dont want another one

I have problem with this statement. I want to have string with 'O' but not with 'G'. Third line WHERE works but Is it possible to write It in a simpler way like one of others?

select * from 
   (select '1XGOX' as x union
    select  '2XOGX' as x union
    select  '3XO' as x union
    select  '4XG' as x) as y
where
--x like '%[O^G]%'
--x like '%[O]%[^G]%'
x like '%[O]%' and x not like '%[G]%'

Desired output 3XO (There's ' O ' and no ' G ' )

Solution

  • Like matchers are NOT regular expressions. You can remove the character class brackets:

    where x like '%O%' and x not like '%G%'

    but that's as close as you'll get.

    If we know more about what these codes represent, and why certain characters appear and seem to be in certain positions, we might be able to do a little better. But as is, this is what you need.