By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

update SQL field with stripped data from other field

P: n/a
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

May 12 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
geekwagon (pr****@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, es****@sommarskog.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
May 12 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.