Replace - 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 2005 Forums

Transact-SQL (2005)

Replace

Author

Topic

itsarnie
Starting Member

18 Posts

Posted - 2009-10-30 : 07:04:55

Hi,

I have got alphanumeric records in a column and I want to replace the aplhabatic part to Zero,whereever they occur.

Is it possible??

Thanks and regards
Arnie,

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-30 : 08:04:01

As far as I know, There is no such built in function.
The following function will do it for you..But It may not be efficient when you use it against bigger data set.

CREATE FUNCTION REPLACE_ALPHA(@InputString Varchar(1000)) RETURNS BIGINT
AS
BEGIN
DECLARE
@CHARPOS INT,
@LENGTH INT
IF @InputString IS NULL OR @InputString='' RETURN 0
IF ISNUMERIC(@InputString)=0
BEGIN
SET @CHARPOS=1
SET @LENGTH=LEN(@InputString)
WHILE(@CHARPOS<=@LENGTH)
BEGIN
IF ASCII(SUBSTRING(@InputString,@CHARPOS,1))<48 OR ASCII(SUBSTRING(@InputString,@CHARPOS,1)) >57
SET @InputString=REPLACE(@InputString,SUBSTRING(@InputString,@CHARPOS,1),'0')
SET @CHARPOS=@CHARPOS+1
END
END
RETURN @InputString
END

SELECT DBO.REPLACE_ALPHA('12A34@6!')

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-30 : 08:09:35

declare @var varchar(20)
declare @pos int
declare @end int

set @var = 'a123as456g789t'
set @pos = 1
set @end = len(@var)

WHILE @pos <= @end
BEGIN
IF SUBSTRING(@var,@pos,1) like '[a-z]'
SET @var = STUFF(@var,@pos,1,'0')
SET @pos = @pos + 1
END
select @var

Jim

Everyday I learn something that somebody else already knew

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 08:31:43

I understood the requirement differently - in that each group of alphas should be replaced with a 0. Obviusly implement as a function.

so 'a111aaahhh111a' becomes '011101110'

DECLARE @AlpaNumeric varchar(50)
DECLARE @FirstChar tinyint
DECLARE @FirstNum tinyint
SET @AlpaNumeric = 'a111aaahhh111a'

WHILE @AlpaNumeric LIKE '%[a-Z]%'
BEGIN
SET @FirstChar = patindex('%[a-Z]%',@AlpaNumeric)
SET @FirstNum = patindex('%[0-9]%',substring(@AlpaNumeric +'0',@FirstChar,50)) + @FirstChar -1
SET @AlpaNumeric = stuff(@AlpaNumeric,@FirstChar,@FirstNum-@FirstChar,'0')
CONTINUE
END

SELECT @AlpaNumeric

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-30 : 09:16:36

or

DECLARE @AlpaNumeric varchar(50)
DECLARE @Numbers varchar(50)
SET @AlpaNumeric = 'a111aaahhh111a'
set @numbers=''
select @numbers =@numbers +case when AlpaNumeric like '[a-z]' then '0' else AlpaNumeric end from
(
select substring(@AlpaNumeric,number,1) as AlpaNumeric from master..spt_values
where type='p' and number between 1 and len(@AlpaNumeric)
) as t
select @numbers

Madhivanan

Failing to plan is Planning to fail

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 10:17:01

assuming its not more than 255 characters :-)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-31 : 02:45:14

quote:
Originally posted by parody

assuming its not more than 255 characters :-)

In SQL Server 2005, it is 2047

Madhivanan

Failing to plan is Planning to fail

parody
Posting Yak Master

111 Posts

Posted - 2009-10-31 : 11:17:00

ooh I didnt know that...

quote:
Originally posted by madhivanan

quote:
Originally posted by parody

assuming its not more than 255 characters :-)

In SQL Server 2005, it is 2047

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-02 : 01:46:05

quote:
Originally posted by parody

ooh I didnt know that...

quote:
Originally posted by madhivanan

quote:
Originally posted by parody

assuming its not more than 255 characters :-)

In SQL Server 2005, it is 2047

Madhivanan

Failing to plan is Planning to fail

No Problem

Madhivanan

Failing to plan is Planning to fail

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-11-02 : 04:43:33

Very nice Jim & Madhivanan....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources