Check to see if the value is alpha

Question

Check to see if the value is alpha

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
October 2, 2002 at 1:56 pm

#78881

Hi,
Is there any way to check if the value is Alpha.
My purpose is to get rid of special character in the value.
Let's say there is a word called 'ASSP '. we know that ASSP is a alpha but there is special character after P. My idea is to get only ASSP not the special character.
Please help me.
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Not sure what the best way is but when you say special character are you talking about the space. If so then RTRIM will remove spaces on the end. Now if you are checking to see if the data is alphanumeric then the best way is to do a ISNUMERIC and ISDATE which if either return 1 may not meet your needs. ISNUMERIC may be all you need thou as Jan 20, 2002 is a date and I believe ISDATE will return 1. Otherwise what is it specific you need?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

Thanks fro your reply.

If it is a space then I can use ltrim fuction but this is other than space. when I use len function of 'ASSP ', I get 5 in length which means that there is some kind of character which can not be seen.

I cannot use ISnumeric or ISdate function because my values are also be in character.

Iam just thinking how to get rid of these special character.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Ok I understand now.

It may be an ASCII character like CHAR(10) or CHAR(13) which can can do a replace on REPLACE(COLNAME,CHAR(10),'') and should care for but the trick is finding out.

This code sample should at least give you an idea of how you can go about it.

DECLARE @t VARCHAR(200)

DECLARE @x int

SET @t = 'AaBCd123,' + CHAR(10) + 'zX_+'

SET @x = 1

WHILE @x <= LEN(@t)

BEGIN

PRINT 'VALUE: ' + CAST(ASCII(SUBSTRING(@t, @x, 1)) AS VARCHAR(4)) + ' EQUALS: ' + SUBSTRING(@t, @x, 1)

SET @x = @x + 1

END

Did not know for sure what version of SQL but if 2000 then you could make a function to run thru and if the character does not match a range of valid ASCII outputs then that value is removed from the output. Otherwise you may need to step thru the records to handle via a cursor or other means. Hopefully this will give you some idea of how to find and eliminate this problem.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

declare @s-2 char(10)

declare @like char(1000)

declare @found int

declare @cmd nvarchar(1000)

set @s-2 = 'abcxxxxx'

set @like = replicate('[a-z]',len(@S))

set @cmd = 'select @found=1 where ''' + rtrim(@s) + ''' not like (''' + rtrim(@like) + ''')'

exec sp_executesql @cmd,N'@found int out',@found out

set @found = 0

if @found = 1

Print 'Found non-alpha data in string'

else

print 'String contains only alpha characters'

set @s-2 = 'abc1232322332'