Get only char values excluding int values

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

Get only char values excluding int values

Author

Topic

Shilpa22
Starting Member

37 Posts

Posted - 2010-06-28 : 04:02:24

Hello All,
I have a varchar column(approvers).for example it returns the below result set,
392813
392813
GroupName_EU
882647
ADGRoup_EU
11955
123456
aaaaaa
From the above result set. I want only the values which have character, excluding all the int values.Pls help in this

THanks

Thanks in Advance
Shilpa

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-28 : 04:28:34

Try this:

One of the simple ways. There may be other ways for achieving the same

select * from yourtable where approvers like '%[a-z]%'

Example:

Declare @r table
(approvers varchar(50))

Insert into @r
Select '392813' union
Select 'GroupName_EU' union
Select '882647' union
Select '123456' union
Select 'ADGRoup_EU' union
Select 'aaaaaa'

select * from @r where approvers like '%[a-z]%'

Regards,
Bohra

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-28 : 04:36:52

What to do with "123abc" values?

N 56°04'39.26"
E 12°55'05.63"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-28 : 04:37:31

Make use of isnumeric()

Before use isnumeric(),go through this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Declare @r table
(approvers varchar(50))

Insert into @r
Select '392813' union
Select 'GroupName_EU' union
Select '882647' union
Select '123456' union
Select 'ADGRoup_EU' union
Select 'aaaaaa'

select * from @r where isnumeric(approvers)=0

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

http://senthilnagore.blogspot.com/

Shilpa22
Starting Member

37 Posts

Posted - 2010-06-28 : 05:15:48

THanks a lot. It worked.

Thanks in Advance
Shilpa

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-28 : 08:59:15

or

select * from @r where approvers like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-28 : 09:02:04

There is still the issue of mixed content; "What do to with values like 'ABC123' "...

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-28 : 09:06:44

quote:
Originally posted by Peso

There is still the issue of mixed content; "What do to with values like 'ABC123' "...

N 56°04'39.26"
E 12°55'05.63"

OP needs to clarify it

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources