replace function

Question

Post reply

replace function

Betty Sun

SSCrazy

Points: 2442
More actions
June 13, 2006 at 4:27 pm

#61910

Hi all,
I tried to update one column data using replace function. but it doesn't work, can you help?
here it is: (the data in content column is a long string and I try to replace string in the middle of the string start with ACCT= and then followed by 4 digits with 'xxxxxxxx', but it doesn't work)
update log set content=replace(content, 'ACCT=[0-9][0-9][0-9][0-9]', 'xxxxxxxx') where tier='C'

Thank you
Betty

Viewing 9 posts - 1 through 8 (of 8 total)

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

Betty Sun SSCrazy Points: 2442 More actions · Answer 1

very strange, I cannot even update.

Is it because it is text field?

what should I do then?

Thank you.

Betty

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

Greg Charles

SSC-Forever

Points: 45403

June 13, 2006 at 5:07 pm

#643513

Try using UPDATETEXT.

Greg

Betty Sun SSCrazy Points: 2442 More actions · Answer 3

Greg,

thank so much. that's good informaiton for me. I just noticed that content column is actually varchar(200).

Do you see any defect in my sql statement?

Thank you.

Betty Sun SSCrazy Points: 2442 More actions · Answer 4

I guess in the replace function, the search string cannot be search string pattern format?

Betty Sun SSCrazy Points: 2442 More actions · Answer 5

what function I can use to delete certain number character in the string if I can locate the start position of the character.

Thankyou

Betty

skeane SSC Eights! Points: 877 More actions · Answer 6

Wildcards are only allowed when using the LIKE operator, as far as I know. REPLACE looks for an exact match of the substring, and while that feels a lot like LIKE, it is not like LIKE, like it or not.

Try using PATINDEX, together with the substring functions, I bet you can get it to work.

edit: I was wrong! Wildcards are also allowed in PATINDEX. Try this:

set NOCOUNT ON

create table #log (content varchar(200), tier char(1))

insert into #log select 'aaaaaaa ACCT=1234 aaaaaaaa', 'A'

insert into #log select 'aaaaaaaaa ACCT=1235 aaaaaa', 'A'

insert into #log select 'ccccccc ACCT=1234 cccccccc', 'C'

insert into #log select 'ccccccccc ACCT=1235 cccccc', 'C'

insert into #log select 'ccNoAcctc cccccc', 'C'

declare @chars2del int --The fixed number of characters to delete

select @chars2del=9

update #log set content =

left(content, PATINDEX('%ACCT=[0-9][0-9][0-9][0-9]%', content)-1) +

right(content, len(content)-(PATINDEX('%ACCT=[0-9][0-9][0-9][0-9]%', content)+@Chars2Del))

where tier='C' and content like '%ACCT=[0-9][0-9][0-9][0-9]%'

select * from #log

drop table #log

You get:

content tier

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

aaaaaaa ACCT=1234 aaaaaaaa A

aaaaaaaaa ACCT=1235 aaaaaa A

ccccccc cccccccc C

ccccccccc cccccc C

ccNoAcctc cccccc C

vadba SSChampion Points: 11133 More actions · Answer 7

What function I can use to delete certain number character in the string if I can locate the start position of the character.

Betty, use the Stuff() function for that, like this:

DECLARE @s-2 varchar(200)

, @startPos int

, @numCharsToDelete int

SET @s-2 = 'ACCT=1033-4055-ABCD-001'

SET @startPos = 6

SET @numCharsToDelete = 5

PRINT @s-2

SET @s-2 = Stuff(@s, @startPos, @numCharsToDelete , '')

PRINT @s-2

Betty Sun SSCrazy Points: 2442 More actions · Answer 8

thank you so much, Addict. That's very good info. I never knew the stuff function before.

Before I got your post. I did in this way.

update log set content='acct='+substring(content, 15, 190) where ...

Thank you for all contribution. I just learnt a lot from you guys.

Betty