Strip data from one column and create another

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

Strip data from one column and create another

Author

Topic

gosman
Starting Member

4 Posts

Posted - 2006-03-05 : 06:37:12

Hi All.

I have a coulmn that contains data in the following format.

COUNTRY/SATE/UNAME

What I would like to do is create another column in the table stripping out the COUNTRY/ and /UNAME

Example
Existing column value
US/NY/BLAH

New Column value
NY

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-05 : 08:50:47

[code]select left(dat, charindex('/', dat) - 1)
from
(
select substring(yourcolumn, charindex('/', yourcolumn) + 1, 10) as dat
from yourtable
) as d[/code]

----------------------------------
'KH'

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-03-05 : 23:38:52

Hi,
First Alter table to add a column..
Alter Table <tablename> add <newcolumn> <datatype>
then you can update the table as

update tablename set newcolumnname = substring( substring( '<columnName>', charindex('/','<columnName>')+1,len('<columnName>')), 1 , charindex('/' , substring( '<columnName>', charindex('/','<columnName>')+1,len('<columnName>')))-1 )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-06 : 00:22:35

or If you use Front End application, you can split and show the specific data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources