CODEWITHSUNDEEP
sqlsql-serverregexdatabase
Anonymous
Anonymous

Reputation: 129

Pattern Matching using Like in SQL Server

I have a column in my table which has values such as

 1VT34
 5AB37-A
 1XY38-2
 0VT31-2
 Science
 History01
 111111

I want to fetch only those values which are of the format such that they are alphanumeric, they start with a number ( from 1 to any number ) they should end with a number, if any number has '-' with it then it should be in this format -> 1VT37-A,1VT38-2. The character after '-' can be either any digit from 1-9 or it should be A.

From the above values I want my query to fetch only

 1VT34
 5AB37-A
 1XY38-2

I have written the following query using like operator -

 select * from Details where Name like '[^0A-Z]%'

but it also fetches 111111 also which I don't want.

then I wrote this to fetch only alphanumerics

   select * from Details where Name like '[^0A-Z]%' and Name like '[^0-9]'

but it doesn't fetch anything.

For fetching name with '-', I wrote the following one

  select * from Details where Name like '[^0A-Z]%' and (Name like '%-A' or Name like '%-[0-9]')

but it only fetched

   5AB37-A
   1XY38-2

and not others.

To avoid this I used another query using UNION -

  select * from Details where Name like '[^0A-Z]%' union 

  select * from Details where Name like '[^0A-Z]%' and Name like '%-A'

but 111111 is still getting fetched.

Upvotes: 0

Views: 529

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Getting exact matches using SQL Server is quite tricky. For correctness, you might consider names like:

1VT34$$
1VT34-1-1

And other weirdnesses. So, based on your description:

I want to fetch only those values which are of the format such that they are alphanumeric, they start with a number ( from 1 to any number ) they should end with a number, if any number has '-' with it then it should be in this format -> 1VT37-A,1VT38-2. The character after '-' can be either any digit from 1-9 or it should be A.

where name not like '%[^-a-zA-Z0-9]%' and  -- only alphanumeric and hyphen
      name like '[0-9]%' and               -- starts with a digit
      name not like '%-%-%' and            -- at most one hyphen
      ( (name like '%[0-9]' and name not like '%-%') or  -- ends with a number if no hyphen
        name like '%-[A1-9]'                             -- other ends with hyphen and A or 1-9
      )

Upvotes: 1

forpas
forpas

Reputation: 164064

For this sample data the pattern should be:

where Name like '[1-9]%[A-Z]%[A1-9]'

or:

Name like '[1-9]%[A-Z]%[1-9]' OR Name like '[1-9]%[A-Z]%-[A1-9]'

See the demo.
Results:

Name
-------
1VT34
5AB37-A
1XY38-2

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try the below -

select * from Details where Name like '[1-9][A-Z]%'

Upvotes: 0

Related Questions