Looping through table records and doing a replace

• TeraByteMe

  SSCarpal Tunnel

  Points: 4171

  More actions

  February 29, 2012 at 9:36 am

  #269232

  I am trying to update filename records in a temp table by using the REPLACE function. I can't think of how to do this all in one statement so I am using seperate update statement. Can anyone think of how I can do this all within one transaction statement?

  Here are the statement I have:

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.pgp','')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.pgp','.txt')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.gpg','.txt')

  Thanks

  A clever person solves a problem. A wise person avoids it. ~ Einstein
  select cast (0x5365616E204465596F756E67 as varchar(128))

• sumitagarwal781

  Default port

  Points: 1493

  More actions

  February 29, 2012 at 9:50 am

  #1453260

  TeraByteMe (2/29/2012)

  ---

  I am trying to update filename records in a temp table by using the REPLACE function. I can't think of how to do this all in one statement so I am using seperate update statement. Can anyone think of how I can do this all within one transaction statement?

  Here are the statement I have:

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.pgp','')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.pgp','.txt')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.gpg','.txt')

  Thanks

  You can not write all three update statement in one query as replace function finds the match then replace it with the string provided in arguement.

  java

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  February 29, 2012 at 10:08 am

  #1453281

  "Can't" is a strong word. Multiple Replaces can be nested, so, with care, this might work.

  Here's a nested Replace example:

  declare @x varchar(100), @y varchar(100)

  set @x = 'abcdefg'

  set @y = replace(replace(replace(@x,'a', 'z'),'b','y'), 'c','x')

  select @x, @y

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  February 29, 2012 at 10:10 am

  #1453284

  TeraByteMe (2/29/2012)

  ---

  I am trying to update filename records in a temp table by using the REPLACE function. I can't think of how to do this all in one statement so I am using seperate update statement. Can anyone think of how I can do this all within one transaction statement?

  Here are the statement I have:

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.pgp','')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.pgp','.txt')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.gpg','.txt')

  Thanks

  Sure you can.

  Do this work for you?

  update #removepgp

  set originalfilename= REPLACE(REPLACE(REPLACE(originalfilename, '.pgp', ''), '.dat.pgp', '.txt'), '.dat.gpg', '.txt')

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• TeraByteMe

  SSCarpal Tunnel

  Points: 4171

  More actions

  February 29, 2012 at 10:17 am

  #1453288

  The nested Replace statements worked great! Thanks Phil and Shawn!

  A clever person solves a problem. A wise person avoids it. ~ Einstein
  select cast (0x5365616E204465596F756E67 as varchar(128))

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  February 29, 2012 at 10:23 am

  #1453295

  TeraByteMe (2/29/2012)

  ---

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.pgp','')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.pgp','.txt')

  update #removepgp

  set originalfilename=REPLACE(originalfilename,'.dat.gpg','.txt')

  You need to be careful on the order here. The second update here will never match any records (barring something unusual like '.dat.pg.pgpp'), because the first one will change all instances of '.dat.pgp' to '.dat'

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA