Varchar with only alphabetical or numerical chars

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)

Varchar with only alphabetical or numerical chars

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-02-12 : 10:01:39

Paul writes "I need to store a field that contains only alphabetical(uppercased) or numerical characters. No spaces or other keyboard typeable characters must be stored.

So far I've got a function that does the following:
return Upper(Replace(@vcCode, ' ', ''))
It can get rid of the spaces and uppercase my varchar, but all the other garbage is still in it.

Is there a way of filtering out the unwanted characters from the varchar?"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 10:07:24

Loop through all characters in the string and STUFF out all characters not within 65-90 ASCII range (A-Z), and not within 48-57 ascii range (0-9).

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-12 : 10:47:27

Add proper validation control in the front-end so that the bad data doesn't go to the database in the first place.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-12 : 11:01:30

You can also add a check constraint to make sure you don't get any more bad data in the column:

Alter table Mytable
add constraint 
CHK_MyCol_only_letters_or_Numbers
check
(case MyCol like '%[a-z0-9]%' then 0 else 1 end = 1)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:07:09

No COLLATE to handle UpperCase vs LowerCase characters?
What about the data './1a'?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:10:40

Alter table Mytable
add constraint
CHK_MyCol_only_letters_or_Numbers
check
(case MyCol like '%[^a-z0-9]%' then 0 else 1 end = 1)

Peter Larsson
Helsingborg, Sweden

PaulFourie
Starting Member

1 Post

Posted - 2007-02-13 : 07:09:52

Thanks for all the replies.

This is what I did eventually:

ALTER FUNCTION [dbo].[fCorrectVcCode] (@vcCode varchar(50))
RETURNS varchar(50) AS
BEGIN
declare
@vcMyNewString varchar(50),
@iPosition int

set @iPosition = 1
set @vcMyNewString = ''

while @iPosition <= Len(@vcCode)
begin
if Ascii(SubString(@vcCode, @iPosition, 1)) >= Ascii('0')
and Ascii(SubString(@vcCode, @iPosition, 1)) <= Ascii('9')
begin
set @vcMyNewString = @vcMyNewString + SubString(@vcCode, @iPosition, 1)
end
if Ascii(SubString(@vcCode, @iPosition, 1)) >= Ascii('A')
and Ascii(SubString(@vcCode, @iPosition, 1)) <= Ascii('Z')
begin
set @vcMyNewString = @vcMyNewString + SubString(@vcCode, @iPosition, 1)
end
if Ascii(SubString(@vcCode, @iPosition, 1)) >= Ascii('a')
and Ascii(SubString(@vcCode, @iPosition, 1)) <= Ascii('z')
begin
set @vcMyNewString = @vcMyNewString + Upper(SubString(@vcCode, @iPosition, 1))
end

set @iPosition = @iPosition + 1
end

return @vcMyNewString
END

My front-end does filter out the unwanted characters, but I needed a SQL answer to handle import data (direct import from csv files).

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2007-02-13 : 12:28:18

Hi all,

I've just created a function for handling this kind of problem in general, because I've seen it a fair bit.

The function is here...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

And here's an example for this case...

DECLARE @s VARCHAR(50)
SET @s = './ 1a )*^s8s 9a  rK"£%s SdF098S dsfjds@~luir382 {]'
SELECT UPPER(dbo.fn_FilterString(@s, '[a-z0-9]', 0))

--Result is '1AS8S9ARKSSDF098SDSFJDSLUIR382'

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources