numeric Convertion - 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)

numeric Convertion

Author

Topic

rauof_thameem
Starting Member

31 Posts

Posted - 2007-05-23 : 00:27:44

Hi all..,

i have a field which contains both numeric and alpha numeric values, i want to select only those records which has numeric values..what i have to do plz suggest me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 00:29:08

you can use the isnumeric() function

rauof_thameem
Starting Member

31 Posts

Posted - 2007-05-23 : 00:30:10

Thanks khtan..,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-23 : 01:19:07

Remember though that IsNumeric() can not be trusted all the time.
Take some time to read this article: [url]http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-23 : 01:22:55

Yes. indeed. See if this function is suitable for your requirement.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59049

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-23 : 01:45:09

[code]

Select val from
(
Select 'test' as val
union all
Select '12ab'
union all
Select '9872fd54'
union all
Select 'k1200'
union all
Select '677'
) T
where val not like '%[^0-9]%'
[/code]

Madhivanan

Failing to plan is Planning to fail

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-05-23 : 10:27:58

quote:

Originally posted by madhivanan



Select val from
(
	Select 'test' as val
	union all
	Select '12ab' 
	union all
	Select '9872fd54' 
	union all
	Select 'k1200' 
	union all
	Select '677' 
) T
where val not like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

what if ...

Select val from
(
Select 'test' as val
union all
Select '12ab'
union all
Select '9872fd54'
union all
Select 'k1200'
union all
Select '67.7'
) T
where val not like '%[^0-9]%'

it will check only for int. even 67.7 is number, right?

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-23 : 10:44:29

[code]Select val from
(
Select 'test' as val
union all
Select '12ab'
union all
Select '9872fd54'
union all
Select 'k1200'
union all
Select '67.7'
) T
where val not like '%[^0-9.]%'
[/code]

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-23 : 11:35:21

What about this, Madhi?

Select val from
(
Select 'test' as val
union all
Select '12ab'
union all
Select '9872fd54'
union all
Select 'k1200'
union all
Select '-28'
) T
where val not like '%[^0-9.]%'

or what about badly formed numbers like 12.2.4? This is not a valid number but it will show up in Not Like test because Like does not provide regular expression capability.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-24 : 01:29:47

Then you need to consider all kinds of possibilities and change the code accordingly

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources