|
|
Posted by Erland Sommarskog on 10/02/55 11:47
geekwagon (prourl@gmail.com) writes:
> 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
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@sommarskog.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
Navigation:
[Reply to this message]
|