update columns by splitting content of another col

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 columns by splitting content of another col

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-08-05 : 17:26:49

Hi all,
I have a table with the following columns info, description, comments, title, mediaid
the column info has the following content (description|comments|title|mediaid)
ie. it has the information of the other columns separted by |

how i can update the table setting the values of description to the first portion of info column etc.
Thanks

sarah

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-05 : 17:44:02

This is ugly, but it works:

DECLARE @t TABLE (info VARCHAR(255), description VARCHAR(255), comments VARCHAR(255), title VARCHAR(255), mediaid VARCHAR(255))
INSERT @t(info) VALUES('description|comments|title|mediaid')

UPDATE @t SET description=LEFT(info,CHARINDEX('|',info)-1),
mediaid=REVERSE(LEFT(REVERSE(info),CHARINDEX('|',REVERSE(info))-1)),
comments=LEFT(STUFF(info,1,CHARINDEX('|',info),''),CHARINDEX('|',STUFF(info,1,CHARINDEX('|',info),''))-1),
title=REVERSE(LEFT(STUFF(REVERSE(info),1,CHARINDEX('|',REVERSE(info)),''),CHARINDEX('|',STUFF(REVERSE(info),1,CHARINDEX('|',REVERSE(info)),''))-1))

SELECT * FROM @t

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-08-05 : 18:28:07

Thanks

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-08-05 : 18:28:39

I also find another way using a split function
Thanks again

sarah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources