CODEWITHSUNDEEP
sqlsql-server-2012substring
Mar1009
Mar1009

Reputation: 811

Find a string inside a string and update in SQL Server

I have a table like this :

create table Fbank (id int, COCODE nvarchar(max))

insert into Fbank 
values (1, '<row xml:space="preserve" id="174580000041250.040002">
  <c1>HTG115960001</c1>
  <c2>14013514,48</c2>
  <c3>222</c3>
  <c4>BAL MIGRATED</c4>
  <c5>NULL</c5>
  <c6>NULL</c6>
  <c7>NULL</c7>
  <c8>9900</c8>
  <c9>11596</c9>
  <c10>20151017</c10>
  <c11>HTG</c11>
  <c12>NULL</c12>
  <c13>NULL</c13>
  <c14>FT1529010083</c14>
  <c15>1</c15>
  <c16>FT1529010083</c16>
  <c17>FT</c17>
  <c18>20151017</c18>
  <c19>NULL</c19>
  <c20>0</c20>
  <c21>5033_INPUTTER__OFS_AUTO.FT</c21>
  <c22>5033_INPUTTER_OFS_AUTO.FT</c22>
  <c23>1510181127</c23>
  <c24>HG0010001</c24>
</row>')

I need to replace the string inside the tag c24 tag..

Excepted output: I need to replace the letter 'G' to be replaced with 'T' like this inside c24 tag - HT0010001 and all the rest should be same unchanged...

What I tired:

declare @searchText varchar(20) = (
select SUBSTRING(COCODE,CHARINDEX('<c24>', COCODE),  7)
from FBNK_EB_SFF_ACCT_HI000)

update Fbank 
set cocode = STUFF(@searchText, CHARINDEX('<c24>', COCODE), 1, 'T')

Upvotes: 0

Views: 29

Answers (1)

pwilcox
pwilcox

Reputation: 5763

If the code always begins with 'H', then I imagine it's easier to just have '<c24>H' be part of the string to replace, but then add it back again:

update Fbank 
set    COCODE = replace(cocode, '<c24>HG', '<c24>HT');

If it doesn't, then I'd say you're on the right track with charindex and substring. But use patindex instead, and update fbank directly, don't put the search term into any variable. If you're trying to split up your logic into parts, consider cross apply:

update      fb 
set         cocode = stuff(cocode, ix, 1, 'T')
from        Fbank fb
cross apply (select ix = patindex('%<c24>%', cocode) + 6) ap
where       substring(cocode, ix, 1) = 'G'

Upvotes: 3

Related Questions