SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update column by parsing another column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Apples
Posting Yak Master

146 Posts

Posted - 05/13/2013 :  00:08:54  Show Profile  Reply with Quote
So I have this table and data in my database:

------------------------------
tblPhotos
------------------------------
ID | Title | Description
------------------------------
1 | NULL | Some data - Title goes here - more title here - Photographer: John Doe
2 | NULL | Some data - Photographer: Jane Doe
3 | NULL | Some data - Title goes here - Photographer: John Doe Jr

The data in the Description field comes in two formats:

{GARBAGE DATA} - {TITLE DATA} - Photographer: ..., or
{GARBAGE DATA} - Photographer: ...

So basically, I have thousands of rows, where the Title field is empty. I need to somehow pull it out of the Description field.

The Title exists between the first dash and the and the last dash of the Description field. Here's what the data should look like:

------------------------------
tblPhotos
------------------------------
ID | Title | Description
------------------------------
1 | Title goes here - more title here | Some data - Title goes here - more title here - Photographer: John Doe
2 | NULL | Some data - Photographer: Jane Doe
3 | Title goes here | Some data - Title goes here - Photographer: John Doe Jr

How can I make a script that will parse a column, and update another column with that parsed data?


visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/13/2013 :  00:27:57  Show Profile  Reply with Quote
first you need to determine if data coming in Decsription column has consistent format.Once thats fixed, you can apply a logic based on CHARINDEX and SUBSTRING functions to get the desired part out of the string

something like

UPDATE table
SET Title =LEFT(STUFF(Description,1,CHARINDEX('-',Description),''),CHARINDEX('-',Description)-1)
WHERE LEN(Description) - LEN(REPLACE(Description,'-',''))>1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000