like condition - SQL Server Forums

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

like condition

Author

Topic

ArcSlayer
Starting Member

4 Posts

Posted - 2011-05-31 : 04:24:35

i have data:
5 L
A21 L
5S4 L
10 L
WT9 L

i want to select any data that has L on the end and any digit (number only) on the front.

with the data above, i want to get result:
5 L
10 L

is there any parameter other than % for like condition? or maybe there is any other solution?
thx.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-31 : 04:30:06

like '[0-9]%L'

No, you're never too old to Yak'n'Roll if you're too young to die.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-05-31 : 04:34:49

You might need a NOT LIKE afterwards though to get rid of 5S4 L

like '[0-9]%L' and not like '[0-9][a-z]%L'

ArcSlayer
Starting Member

4 Posts

Posted - 2011-05-31 : 04:43:31

that's not what i mean.

xxxL
i want to get the result that xxx is number only (any digit. it can be 4, 27, or 749)
and with L in the end.

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-31 : 05:16:10

it works:

declare @s as varchar(5)
set @s = '5 L'

select @S

IF (@S LIKE '[0-9]%L')
BEGIN
PRINT 'OK'
END

If you want to remove L at the end, then either replace or use substring

Where software development knowledge meets the reader

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-05-31 : 06:51:28

Why is this wrong from your original post?


DECLARE @tmp TABLE ( yourval Varchar(100))
INSERT INTO @tmp
SELECT '5 L'
UNION SELECT 'A21 L'
UNION SELECT '5S4 L'
UNION SELECT '10 L'
UNION SELECT 'WT9 L'

SELECT * FROM @tmp WHERE yourval LIKE '[0-9]%L' AND yourval NOT LIKE '[0-9][a-z]%L'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources