CODEWITHSUNDEEP
sqlsql-servert-sqlsql-likevarchar
Dashovsky
Dashovsky

Reputation: 137

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%'?

Upvotes: 0

Views: 123

Answers (3)

Vasily
Vasily

Reputation: 5782

here solution using like 

SELECT *
    FROM (
    VALUES ( 'R123'),
           ( 'R12T' ),
           ( 'RT12' )
    ) AS t(test)
    where test like 'R[0-9][0-9][0-9]'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 4

Dashovsky
Dashovsky

Reputation: 137

like 'S[0-9%]'

Friend came up with the solution, thanks anyway

Upvotes: -1

Related Questions