CODEWITHSUNDEEP
sqlsql-servert-sql
Shahsra
Shahsra

Reputation: 1009

Problem in splitting single column values into multiple column values

On Friday I posted a question regarding splitting single column values to multiple column values I got somoe problems there and now in my source table I got some new feilds added and the tale looks somthing like this:

For Example:

Name
---------------------      
abcd efgh      
ijk lmn     
opq  asd j. asdjja      
asb (asdfas) asd      
asd     
john  tronton III    
john    bradly  0    
user

and expecting output something like this:

first_name             last_name  
-----------            ------------------
abcd                     efgh       
ijk                      lmn
opq                      asdjja      
asb                      asd      
asd                      null     
john                      tronton III    
john                      bradly    
null                      null (because user is not the name)

Need the query in Select statement

The middle name can be omitted (no need for a middle name) The columns are already created and need to insert the data from that single 'Name' column.

Thanks a lot,

Shahsra

Upvotes: 0

Views: 338

Answers (2)

bvr
bvr

Reputation: 4826

;WITH Split_Names (Name, xmlname)
AS
(
    SELECT 
    Name,
    CONVERT(XML,'<Names><name>'  
    + REPLACE(Name,' ', '</name><name>') + '</name></Names>') AS xmlname
      FROM somenames
)

 SELECT       
 xmlname.value('/Names[1]/name[1]','varchar(100)') AS first_name,    
 xmlname.value('/Names[1]/name[2]','varchar(100)') AS last_name
 FROM Split_Names

and also check the link below for reference

http://jahaines.blogspot.in/2009/06/converting-delimited-string-of-values.html

Upvotes: 0

Phil Helmer
Phil Helmer

Reputation: 1240

You should probably look at the answer to this question...

Extracting First Name and Last Name

Upvotes: 1

Related Questions