Search code examples
sqlsql-servert-sqlsql-likevarchar

SQL Like filtering

Welcome!

I am currently working on some C# and I encountered a rather silly issue. I need to fill ListBox with some data from database obviously. Problem is varchar filtering. I need to filter codes and display only the right ones.

Example of codes are: RRTT, RRTR, RT12, RQ00, R100, R200, R355, TY44, GG77 etc. Four digit codes.

I managed to filter only R-codes with simple select * from table1 where code_field like 'R%' but I get both R000 and RQ10 and I need to get R ones followed by numbers only.

So from example:

RRTT, RRTR, RT12, RQ00, R100, R200, R355

Only:

R100, R200, R355

Any ideas what should I add to the like 'R%'?

Solution

  • In SQL Server, you can make use of wildcards. Here is one approach:

    where rcode like 'R___' and       -- make sure code has four characters
      rcode not like 'R%[^0-9]%'  -- and nothing after the first one is not a number

    Or:

    where rcode like 'R[0-9][0-9][0-9]'

    In other databases, you would normally do this using regular expressions rather than extensions to like.