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

Thanks for any help!
Kevin

geekwagon (prourl (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx