character value in a column - 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

General SQL Server Forums

New to SQL Server Programming

character value in a column

Author

Topic

original_caro
Starting Member

6 Posts

Posted - 2010-06-30 : 01:28:14

hi all,

how could I check if a character column has and character value in it?

for example, if a column contains 100, 200, 1AA1, abcd. I want to select value '1AA1' and 'abcd' alone from that column.

Regards,
carro

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-30 : 01:40:08

WHERE ISNUMERIC(YourColumn) = 0

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-30 : 01:40:33

Please note that any solution will perform slow as it'll need to scan the table or the clustered index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

original_caro
Starting Member

6 Posts

Posted - 2010-06-30 : 01:44:02

Thank you :)

Regards,
carro

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-30 : 01:51:36

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

original_caro
Starting Member

6 Posts

Posted - 2010-06-30 : 02:02:08

Could i do this with out using the function, using like or not like?

Regards,
carro

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-30 : 03:04:21

quote:
Originally posted by original_caro

Could i do this with out using the function, using like or not like?

Regards,
carro

You need to use:

Select... from .. where columnname like '%[a-z]%'

I am here to learn from Masters and help new bees in learning.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 03:30:34

ISNUMERIC() isn't reliable.
LIKE '%[a-z]%' doesn't consider each possible non numeric value.
See madhi's solution:
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 05:06:37

or this:
declare @t table (column_x varchar(25))
insert @t
select '1AA1' union all
select '200' union all
select '100' union all
select 'abcd'

select * from @t
where not column_x not like '%[^0-9]%'

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

original_caro
Starting Member

6 Posts

Posted - 2010-06-30 : 07:44:05

Thanks guys.

Regards,
carro

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 07:51:35

welcome