Using a wildcard in a n in command

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

Using a wildcard in a n in command

Author

Topic

Frankp2112
Starting Member

4 Posts

Posted - 2010-03-09 : 04:51:21

Hi,
I am trying to create a query that will only return a valid phone number using the code below

and (HomeTelephone in ('01%', '02%', '07%')

or indivMobileNo in ('01%', '02%', '07%'))

When I incluse this I get no results returned.
can anyone point me in the correct direction of what I am doing wrong?

Thanks

Frank

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 04:57:55

quote:
Originally posted by Frankp2112

Hi,
I am trying to create a query that will only return a valid phone number using the code below

and (HomeTelephone in ('01%', '02%', '07%')

or indivMobileNo in ('01%', '02%', '07%'))

When I incluse this I get no results returned.
can anyone point me in the correct direction of what I am doing wrong?

Thanks

Frank

Could you please show some sample data with expected output...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 04:58:47

You can't use a wildcard in an IN list -- expanding an in is like this:


HomeTelephone in ('01%', '02%', '07%')

Is equivalent to


HomeTelephone = '01%'
OR HomeTelephone = '02%'
OR HomeTelephone = '07%'

You need to use LIKE instead

So you can do this:


and (
    HomeTelephone LIKE '0[127]%'
    or indivMobileNo LIKE '0[127]%'
    )

The square brackets match exactly one of the allowed characters inside.

So those LIKE conditions are

Any string starting with 0 and second character one of (1,2,7) and then anything else for the rest of the string.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources