Select Query - 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 2000 Forums

Transact-SQL (2000)

Select Query

Author

Topic

chetancool
Starting Member

34 Posts

Posted - 2005-07-15 : 03:08:07

Hi Group.

I m having table with one column naming city - varchar. it's row has data like this - Mumbai,delhi,123,bng,456,cal789. Numeric data (123,456) and alphanumeric data has been entered due to user mistake.

Now i want to retrieve data without numeric values. (i.e. - values 123,456 should not appear in result set).

I tried but not succeeded.

Thanks,
Chetan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 03:30:34

Can you show us what you tried?

Madhivanan

Failing to plan is Planning to fail

chetancool
Starting Member

34 Posts

Posted - 2005-07-15 : 03:56:05

hi..

i tried many queries.. two are putted for example.

select convert(int,city) from test4

select * from test4 where convert(int,city)

Thanks.
Chetan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 04:24:58

Try this

Select * from test4 where isNumeric(City)=0

Madhivanan

Failing to plan is Planning to fail

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-15 : 04:25:14

Try this

WHERE ISNUMERIC(City) = 0

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-15 : 04:26:02

Beauty is in the eyes of the beerholder

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 04:31:04

quote:
Originally posted by AndyB13

Beauty is in the eyes of the beerholder

No Problem Andy

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-15 : 04:35:13

look out for this:


select 	isnumeric('124532e2'), isnumeric('144d237'),
	isnumeric('145d234'), isnumeric('ert')

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 04:39:28

Mladen, Good Point

Madhivanan

Failing to plan is Planning to fail

chetancool
Starting Member

34 Posts

Posted - 2005-07-15 : 04:45:51

Thanks .. it worked.

cjain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-15 : 04:49:49

chetancool, If you have records with first and last characters as numbers and alphabets between them(eg given by spirit1), you may use


Select * from test4 where city not like '%[0-9]%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources