Multiple Replace Question

Question

Post reply

Multiple Replace Question

pkrieg

Mr or Mrs. 500

Points: 538
More actions
March 17, 2004 at 3:50 pm

#107244

I have a table that I need to strip ending *s from each row. They could look like this
ABC**~
DE*****~
XXXXXX*~
Z~
I need them to end up as
ABC~
DE~
XXXXXX~
Z~
What is the best way do do this?
Thanks
Phil

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

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

SELECT REPLACE(ABC*******, '*', '')

Kindest Regards,

Calvin Lawson SSChampion Points: 11030 More actions · Answer 2

Technically, if you want to strip ONLY the trailing you'd have to do something like this (I can't figure out how to do this without a loop.

------------

declare @var varchar(30),

@replace char(1)

select @var = 'A*BC*******',

@replace = '*'

while (charindex(@replace, reverse(@var)) = 1 )

begin

select @var = substring(@var, 1, len(@var) - 1)

end

select @var

Signature is NULL

Bert De Haes Hall of Fame Points: 3103 More actions · Answer 3

To strip only the ending *, you could try this script :

create table tsttable ( tstcol varchar(20) )

go

insert tsttable values ('ABC**')

insert tsttable values ('ABC**D')

insert tsttable values ('XXXXXX*')

insert tsttable values ('ABC**Z*')

insert tsttable values ('ABC**D********')

go

select tstcol,newtstcol=

case when patindex('%[^*]%',reverse(tstcol)) > 1

then reverse(substring(reverse(tstcol),patindex('%[^*]%',reverse(tstcol)),8000))

else tstcol

end

from tsttable

result :

tstcol newtstcol

-------------------- --------------------

ABC** ABC

ABC**D ABC**D

XXXXXX* XXXXXX

ABC**Z* ABC**Z

ABC**D******** ABC**D

Calvin Lawson SSChampion Points: 11030 More actions · Answer 4

Wow, that's pretty stinkin' cool; using a bitwise operator with a patindex. very nice; I'm impressed. Obviously I have a lot to learn about parsing strings.

cl

Signature is NULL

gljjr SSChampion Points: 10080 More actions · Answer 5

Bert,

that's great! I always forget about the ^ working in SQL server. BTW: You don't need to put this in a case statement!

IF EXISTS(select * from sysobjects where id = object_id('tsttable'))

DROP TABLE tsttable