isnumeric question 2

[DotNetGnat]

Guys,

I have the a field with the following values

B234567
C123459
N456789
-- and so on

in my queries i used to do ISNUMERIC(myfield, 2, 5) = 0 to eliminate the above records...

but now i got a new record with value as B-2007
and i want to eliminate this too...but when i do ISNUMERIC(myfield, 2, 5)=0 it fails because '-2007' is considered numeric by the function and it returns 1.

any regex or other suggestions to eliminate this record...

thanks

-DNG

 

[SQLDenis]

and not like '%-%'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

I'm a little confused here because ISNUMERIC only accepts 1 argument, and you have 3.

Anyway... there's a trick you can use to prevent IsNumeric from returning true for negative numbers.

For example, -2007 is a number, but --2007 is not, so...

ISNUMERIC('-' + myfield, 2, 5)



-George

"the screen with the little boxes in the window." - Moron

 

[DotNetGnat]

oops...you are right George. Sorry about that...

i meant..
ISNUMERIC(substring(myfield, 2, 5))=0

-DNG

 

[gmmastros]

The same logic applies.

ISNUMERIC([!]'-' + [/!]substring(myfield, 2, 5))=0

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](Data [COLOR=blue]VarChar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'B234567'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'C123459'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'N456789'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'N-20082'[/color])

[COLOR=blue]Select[/color] Data, [COLOR=#FF00FF]IsNumeric[/color]([COLOR=red]'-'[/color] + [COLOR=#FF00FF]SubString[/color](Data, 2, 5)) [COLOR=blue]As[/color] [[COLOR=#FF00FF]IsNumeric[/color]]
[COLOR=blue]From[/color]   @Temp

-George

"the screen with the little boxes in the window." - Moron

 

[SQLDenis]

now add this value

Insert Into @Temp Values('N2e222')

and then grab George's ISInt function from the FAQs

why not just do this instead?

where data like'[a-Z][0-9][0-9][0-9][0-9][0-9]%'

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('B234567')
Insert Into @Temp Values('C123459')
Insert Into @Temp Values('N456789')
Insert Into @Temp Values('N2e222') --Oops

Select Data, IsNumeric('-' + SubString(Data, 2, 5)) As [IsNumeric]
From   @Temp

Select Data
From   @Temp
where data like'[a-Z][0-9][0-9][0-9][0-9][0-9]%'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[DotNetGnat]

Thanks George and Denis.

I was thinking George's answer would include a regex

Any ways, have a star.

Thanks

-DNG

 

[DotNetGnat]

Thanks Denis. I will look into your code.

Thanks for your prompt replies guys.

Have a star.

-DNG