filter on isnumeric - 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

SQL Server 2005 Forums

Transact-SQL (2005)

filter on isnumeric

Author

Topic

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-14 : 07:21:33

hi

when i got

ABC-00
ABC-11
ABClitest
BMA_EXTRA

i only want the
00 and 11

So i used following query

case when table like '%ABC%' then right(table,2) else table end.

But i get everthing then.
00
11
en ST (from ABCLITEST).

so then i tried
case when table like '%ABC%' and isnumeric(right(table,2) then right (table,2)else table end.

but i get error of "an expression of non-boolean type specified in a context where a condition is excpected near 'then'

IBoonZ
Yak Posting Veteran

53 Posts

Posted - 2009-04-14 : 07:26:52

found it :).
If mods want they can delete is, maybe i can help other ppl with following answer
case isnumeric(right(table,2)) when 1 then right(table,2)
else table end

hambalang
Starting Member

4 Posts

Posted - 2009-04-14 : 07:28:32

ISNUMERIC returns 1 when the input expression evaluates to a valid integer. So, let's try "case when table like '%ABC%' and isnumeric(right(table,2) = 1 then right (table,2)else table end"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:28:41

Won't work.
Try with this sample data
ABC-0D

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-14 : 07:29:41

[code]SELECT RIGHT(Col1, 2)
FROM Table1
WHERE Col1 LIKE '%[0-9][0-9]'[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources