Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

REPLACE Expand / Collapse
Author
Message
Posted Friday, November 01, 2013 1:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 6:32 AM
Points: 29, Visits: 70
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
Post #1510525
Posted Friday, November 01, 2013 1:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
update table_name
set col1 = replace(col1,'XXX','')


are you using something like this?


Regards
Durai Nagarajan
Post #1510526
Posted Wednesday, November 06, 2013 5:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 500, Visits: 2,291
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.




-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1512041
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse