Earlier, I have written a blog post about how to split a single row data into multiple rows using XQuery. Today, I came across a situation where I had to split a single column data into multiple columns using delimiter.
Lets me create a sample to demonstrate the solution.
Sample :
USE TEMPDB GO DROP TABLE [dbo].[tbl_Employee] GO CREATE TABLE [dbo].[tbl_Employee]( [Employee ID] INT IDENTITY(1,1) , [Employee Name] VARCHAR (100) NOT NULL) GO INSERT INTO dbo.[tbl_Employee] ([Employee Name]) VALUES ('Andreas Berglund T') GO INSERT INTO dbo.[tbl_Employee] ([Employee Name]) VALUES ('Sootha Charncherngkha T') GO INSERT INTO dbo.[tbl_Employee] ([Employee Name]) VALUES ('Peng Wu') GO --Browse the data SELECT * FROM dbo.[tbl_Employee] GO
Solution :
Given below is the solution, where we need to convert the column into xml and then split it into multiple columns using delimiter. You can use any delimiter in the given below solution. In my sample, I used a blank space as a delimiter to split column into multiple columns.
USE TEMPDB GO DECLARE @delimiter VARCHAR(50) SET @delimiter=' ' -- <=== Here, you can change the delimiter. ;WITH CTE AS ( SELECT [Employee ID], [Employee Name], CAST('' + REPLACE([Employee Name], @delimiter , '') + '' AS XML) AS [Employee Name XML] FROM [tbl_Employee] ) SELECT [Employee ID], [Employee Name], [Employee Name XML].value('/M[1]', 'varchar(50)') As [First Name], [Employee Name XML].value('/M[2]', 'varchar(50)') As [Last Name], [Employee Name XML].value('/M[3]', 'varchar(50)') As [Middle Name] FROM CTE GO
Let me know if you come across this situation and its solution.
it doesn’t split.it returns null
Thank you so much! This was the only solution I could find that worked after trying at least 10 others.
Dear Muhammad,
This code works perfect, thanks for sharing. I would like to be able to import the resulting table into MS access, can you tell me why this doesn’t work? I tried to create a view, however the “Declare” function doesn’t work then.
Best regards,
I have copied the same script, new columns created but with NULL values..
Hello! I used this post to try to solve a similar issue. I am trying to get client information in a select query and it includes a long address. In the database, this address is separated by Char(13). Using your code I can indeed split the address. Many thanks for that. However I really need to use this coding into a select statement and preferably in a “Case When Then” => the source application has two different address sections so if section 1 is empty, I need to take the second section, hence the Case.
A Case statement however only allows one column as a result and not multiple columns.
Do you know a solution for this? Many thanks!
This script returns Nulls values, because the script before the screenshot is not the same executed and shown in the screenshot.
Line 8 must be:
CAST(” + REPLACE([CriteriaTokens], @delimiter , ”) + ” AS XML)
[…] SQL SERVER – How to split one column into multiple columns – SQL SERVER – How to split one column into multiple columns. August 22, 2015 by Muhammad Imran […]
Hi..good work..but my case little bit different , I have uneven spaces between words.i.e. between firstname and middle name one tab , between mid name and last name multiple tabs … more space…can you suggest anything ??? for 1 record I may have 5tabs space, for another record I may have 10 tabs space…uneven space…
Thanks Brother…
This code works perfectly..
I was trying this since 1 week but was unable to get the desired result.
Thanks for your post.
the code gives error Msg 9400, Level 16, State 1, Line 3
XML parsing: line 1, character 35, unexpected end of input …..please suggest
on January 8, 2017 at 10:21 am | ReplyPranay
Your comment is awaiting moderation.
how can split this following record in 3 diff column ( City,State,Pin)
we have table address with one column
record like this
Pune MH 411028
Pimpri Chinchwad MH 411013
hutto FL 02134
we have one more table with name state with two column
record like this
Maharashtra MH
Florida FL
Gujrat GJ
and I want result like this ( address must be spitted in 3 diff column )
record like this ( 3 diff column with name ( City,State,Pin)
Pune MH 411028
Pimpri chinchwad MH 411013
Hutto FL 02134
it works fine but one catch the CAST function missing xml tags.. if you couldn’t figure out yourself refer the screenshot image just below script.
here is the script
CAST(” + REPLACE([Employee Name], @delimiter , ”) + ” AS XML)
Here I have used “Pipeline demiliter”. Splitting a single column where its values delimited by pipelines into multiple columns in SQL.
Working Solution
DECLARE @delimiter VARCHAR(50)
SET @delimiter=’|’ — <=== Here, you can change the delimiter.
;WITH CTE AS
(
SELECT
ID,
Value,
CAST('’ + REPLACE(Value, @delimiter , ”) + ” AS XML)
AS [Delimited_Key_Value]
FROM #Temp_Tbl
)
SELECT
ID,
Value,
[Delimited_Key_Value].value(‘/M[1]’, ‘varchar(50)’) As [ID],
[Delimited_Key_Value].value(‘/M[2]’, ‘varchar(50)’) As [Type],
[Delimited_Key_Value].value(‘/M[3]’, ‘varchar(50)’) As [Name]
FROM CTE
GO
Awesome, works like a charm and made my task very easier.
This solution is awesome, Works for me. But one small problem. I have few rows which as value as 12213\21313\21323\
So when I run this query it splits all of them including the last \ . As there is no value after that , it is returning empty cell . Can i know how to make the empty cell as NULL so that I need not show them in the result ?