looking for numerics - 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)

looking for numerics

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-04 : 07:44:35

i want to in my stored procedure check the body field and if there are 10 numeric numbers then marked flagged=1

is this easy to do or should it be done in the vb code?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-04 : 07:46:50

Sorry, but your problem isn't described very clear.
Could you give an example please?

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-04 : 07:49:04

meaning it should look through my ntext field called @body
if it finds a numeric 10 digit number 3456789876 then it should mark status=1
otherwise status=0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-04 : 07:49:13

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

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-04 : 08:01:21

Try like this too

update table_name set column_name =
case when ISNUMERIC('3456789876')=1 and len ('3456789876')=10 then '1'
else '0' end where .....

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-04 : 08:02:12

but it should be any 10 digit number in the text
so should i do
where col like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-04 : 08:08:13

quote:
Originally posted by esthera

but it should be any 10 digit number in the text
so should i do
where col like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

Yes, do it like madhi wrote because ISNUMERIC isn't reliable for your problem.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-04 : 08:10:40

quote:
Originally posted by esthera

but it should be any 10 digit number in the text
so should i do
where col like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'

Yes it is

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-04 : 08:12:00

Why ISNUMERIC() is not reliable?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-04 : 08:14:34

quote:
Originally posted by senthil_nagore

Try like this too

update table_name set column_name =
case when ISNUMERIC('3456789876')=1 and len ('3456789876')=10 then '1'
else '0' end where .....

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

ISNUMERIC is not reliable.
See here

select data from
(
select '2983471238' as data union all
select '17612399d2'
) as t
where ISNUMERIC(data)=1 and len (data)=10

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-04 : 08:17:47

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

Try like this too

update table_name set column_name =
case when ISNUMERIC('3456789876')=1 and len ('3456789876')=10 then '1'
else '0' end where .....

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

ISNUMERIC is not reliable.
See here

select data from
(
select '2983471238' as data union all
select '17612399d2'
) as t
where ISNUMERIC(data)=1 and len (data)=10

Madhivanan

Failing to plan is Planning to fail

Oh.. Thanks Madhi,

Is it a bug or something else??? Whats the purpose of isnumeric()?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-04 : 08:20:08

quote:
Originally posted by senthil_nagore

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

Try like this too

update table_name set column_name =
case when ISNUMERIC('3456789876')=1 and len ('3456789876')=10 then '1'
else '0' end where .....

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

ISNUMERIC is not reliable.
See here

select data from
(
select '2983471238' as data union all
select '17612399d2'
) as t
where ISNUMERIC(data)=1 and len (data)=10

Madhivanan

Failing to plan is Planning to fail

Oh.. Thanks Madhi,

Is it a bug or something else??? Whats the purpose of isnumeric()?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-04 : 08:27:54

Great Work. Thanks Madhi

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources