REPLACE

• fgrubercpa

  Say Hey Kid

  Points: 661

  More actions

  November 1, 2013 at 1:55 am

  #278662

  I have a char column with some misc text I want to get rid of.

  I am using SELECT REPLACE, etc...

  which produces the desired output, but doesn't update the actual table.

  dumb q, but how do i get the actual table column updated?

  thx

  f

• durai nagarajan

  SSCoach

  Points: 15778

  More actions

  November 1, 2013 at 1:57 am

  #1663358

  update table_name

  set col1 = replace(col1,'XXX','')

  are you using something like this?

  Regards
  Durai Nagarajan

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  November 6, 2013 at 5:23 pm

  #1664592

  I would add that you can also do it like this:

  --sample data

  SELECT * INTO #test

  FROM (VALUES (1,'blah AAA'),(2,'bbb bbb bbb'),(3,'AAA ccc')) t(id,val);

  SELECT * FROM #test;

  WITH your_table AS

  (

  SELECT * FROM #test WHERE val like '%AAA%'

  )

  UPDATE your_table

  SET val=REPLACE(val,'AAA','[xxx]')

  SELECT *

  FROM #test

  I like this technique because, in SQL Server Management Studio, you can highlight & execute just the SELECT * FROM #test WHERE val like '%AAA%' portion of the statement to see what rows are going to be updated.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001