update SQL field with stripped data from other field

**Erland Sommarskog** · May 12 '06, 10:25 PM

Re: update SQL field with stripped data from other field

geekwagon (prourl@gmail.c om) writes:[color=blue]
> Not a SQL guy but can do enough to be dangerous :)
>
> Trying to update a record. We have records that have a field with data
> surrounded by some comment text such as *** Previous Public Solution
> *** Start and *** Previous Public Solution *** End . What I am trying
> to do is write a SQL statement that will:
>
> Check that field C100 = TICKET0001 (to test with one record before
> running on whole db)
> Check that field C101 is = Closed
> Check that field C102 is null
> Copy field C103 data to field C102 and strip out any words such as ***
> Previous Public Solution *** Start and *** Previous Public Solution
> *** end[/color]

UPDATE tbl
SET C102 = replace(replace (C103,
'*** Previous Public Solution *** Start',
'*** Previous Public Solution *** end))
WHERE C100 = 'TICKET0001'
AND C101 = 'Closed'
AND C102 IS NULL

This assumes that the text is exactly as you posted, and with no
variation in spacing etc. I would guess that real world is different.
Unfortunately that can be difficult to handle in SQL. If you are on
SQL 2005 you could possible write a CLR that works with regular
expressions. Else the cleanup is probably best done client-side.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

update SQL field with stripped data from other field

update SQL field with stripped data from other field

Comment