Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Update column by parsing another column

Author  Topic 

Apples
Posting Yak Master

146 Posts

Posted - 2013-05-13 : 00:08:54
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

52326 Posts

Posted - 2013-05-13 : 00:27:57
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
   

- Advertisement -