replace function
-
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
-
very strange, I cannot even update.
Is it because it is text field?
what should I do then?
Thank you.
Betty
-
Try using UPDATETEXT.
Greg
Greg
-
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.
-
I guess in the replace function, the search string cannot be search string pattern format?
-
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
-
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
- 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
-
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply