Dr. John A Zoidberg Dr. John A Zoidberg - 7 months ago 19
SQL Question

SQL command to map data from one column to another

Suppose my table looks like this

[ name, number, firstname]
[foo:bar, 123, ]
[bar:foo, 456, ]

I want to split the name field at ':' and use the first part as the 'firstname' column. Furthermore, I want this to be permanent, rather than just a result set. How would I do that?

I'm using SQL Server 2008.

I'm aware that variants of this question have been asked before, but they all seem to deal with merely selecting the data, and I mean to update it.


You could create a view that does this then use the view for future selects.

CREATE VIEW split name AS
SUBSTRING(name, 1, CHARINDEX(':',  name, 1) - 1) as firstname, 
RTRIM(SUBSTRING(name,  CHARINDEX(':',  name, 1)+1,100)) as surname, number
FROM myTable


UPDATE myTable
SET firstname = SUBSTRING(name, 1, CHARINDEX(':',  name, 1) - 1) 
FROM myTable