Regular Expression in Sql Server

Question

I have special selection criterion for particular column and need the regular epxression for that. e.g: A123456, S008942 this id's should get selected only The first letter is alphabet and next 6 letters are digits. It will always be like total of 7 letters (1Alphabet+6NumericDigits)

Any help is appreciated. Thanks

Remember to accept the answer that solved your problem. stackoverflow.com/faq#howtoask — Miika L.
– Miika L., Commented Apr 27, 2012 at 6:19

Miika L. · Accepted Answer · 2012-04-27 06:05:00Z

3

Using SQL server pattern matching: http://msdn.microsoft.com/en-us/library/ms187489%28SQL.90%29.aspx

SELECT *
FROM [table]
WHERE [column] LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9]'

It is unfortunately limited, but maybe it will get what you need?

answered Apr 27, 2012 at 6:05

Miika L.

3,3491 gold badge27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

COLD TOLD · Accepted Answer · 2012-04-27 05:49:38Z

1

try this maybe it helps

"^[A-Z]{1}[0-9]{6}$"

answered Apr 27, 2012 at 5:49

COLD TOLD

13.6k3 gold badges37 silver badges53 bronze badges

7 Comments

COLD TOLD Over a year ago

it defines for only one letter

Pratik Over a year ago

SELECT * FROM tblUserData WHERE ID Like '^[A-Za-z]\d{6}$' SELECT * FROM tblUserData WHERE ID Like '^[A-Za-z]{1}[0-9]{6}$' This is also not working :(

Pratik Over a year ago

Both the above queries are not working for me...i am using SQL server 2008

COLD TOLD Over a year ago

SQL SERVER 2008 does not supports regular expressions

Pratik Over a year ago

Its a simple LIKE expression i am writing ...it should work atleast

|

shift66 · Accepted Answer · 2012-04-27 05:44:53Z

0

This is your pattern: [A-Z]\d{6}
If first letter can be lowercase pattern is: [A-Za-z]\d{6}

[A-Z] means an uppercase letter.
[A-Za-z] means an uppercase or lowercase letter.
\d means a digit.
{6} after it means exactly 6 times. (so \d{6} means 6 digits)

answered Apr 27, 2012 at 5:44

shift66

12k13 gold badges54 silver badges83 bronze badges

7 Comments

Pratik Over a year ago

Thanks for your response Ademiban...but its not working in the SQL query SELECT * FROM tableUserdata WHERE ID Like '[A-Z]\d{6}'

shift66 Over a year ago

Are you sure...? try to put ^ on the start and $ at the end: ^[A-Z]\d{6}$

Pratik Over a year ago

This is also not working. Any conversion do i need to do before i execute this query

shift66 Over a year ago

I haven't seen your code and can't say why it's not working but this pattern is 100% right. You can check it here: regexpal.com

Pratik Over a year ago

Yes i am also sure on pattern .. there is not code actually i needed this for select statement in SQL Server 2008

|

Collectives™ on Stack Overflow

Regular Expression in Sql Server

3 Answers 3

Comments

7 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related