Regular expression for SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Regular expression for SQL query

Author

Topic

shahid09
Starting Member

35 Posts

Posted - 2008-07-02 : 09:51:17

Hi All,

I am running SQL query. I want to get all Contract IDS from the table where second last digit is 5.

Any suggestion how can i use regex here ?
Our contract ID is in this format.

R51001550
R23434567
R12489750
R23456560

Thanks,
Shahid

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 09:53:50

One of the methods

where col like '%5[0-9]'

Madhivanan

Failing to plan is Planning to fail

shahid09
Starting Member

35 Posts

Posted - 2008-07-02 : 10:06:14

Hi madhivanan,

Thanks for the reply. I am getting following error.
"The data types of the operands for the operation" are not compatible.

Thanks
i used this code

SELECT CONTRACT_ID
FROM Table_cont
WHERE CONTRACT_ID = '%5[0-9]'

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-02 : 10:11:29

Madhi's example said LIKE not =
i.e...
WHERE CONTRACT_ID LIKE '%5[0-9]'

Em

shahid09
Starting Member

35 Posts

Posted - 2008-07-02 : 10:19:40

Thanks

Now throwing following error

"A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operant is not a string'

SELECT CONTRACT_ID
FROM Table_cont
WHERE CONTRACT_ID Like '%5[0-9]'

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-02 : 10:22:03

SELECT CONTRACT_ID
FROM Table_cont
WHERE SUBSTRING(CONTRACT_ID,8,1) = '5'
If contract is always 9 characters

SELECT CONTRACT_ID
FROM Table_cont
WHERE SUBSTRING(REVERSE(CONTRACT_ID),2,1) = '5'
if it is not

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources