How to Split a Column value in Sql Server

Question

1.00/5 (2 votes)

See more:

i have a column value in a table like 1,2,3. i want to split it into three rows in sql server,
if anyone knows Let me know

Posted 11-Jun-14 1:06am

meranaamshahul

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Answer 1 · 2014-06-11T21:47:00

Try This,

CREATE FUNCTION [Split]
   (
       @String varchar(8000),
       @Delimiter char(1)
   )
   returns @temptable TABLE (items varchar(8000))
   as
   begin
       declare @idx int
       declare @slice varchar(8000)

       select @idx = 1
           if len(@String)<1 or @String is null  return

       while @idx!= 0
       begin
           set @idx = charindex(@Delimiter,@String)
           if @idx!=0
               set @slice = left(@String,@idx - 1)
           else
               set @slice = @String

           if(len(@slice)>0)
               insert into @temptable(Items) values(@slice)

           set @String = right(@String,len(@String) - @idx)
           if len(@String) = 0 break
       end
   return
   end

And

SQL

SELECT * from Split('1,2,3',',')

Hope this will help you.

Andrius Leonavicius · Answer 2 · 2014-06-11T01:29:00

Solution 3

Hi,

You can do it like this:

SQL

DECLARE @columnValue VARCHAR(MAX) = '1,2,3';
DECLARE @xmlList XML = CAST('<i>' + REPLACE(@columnValue, ',', '</i><i>') + '</i>' AS XML);

SELECT x.i.value('.', 'VARCHAR(MAX)') AS [Values]
FROM @xmlList.nodes('i') x(i);

Result:

XML

Values
1
2
3

I would recommend you to read this article: Delimited String Tennis Anyone?[^]

Posted 11-Jun-14 1:29am

Andrius Leonavicius

Updated 11-Jun-14 3:25am

v2

Comments

meranaamshahul 11-Jun-14 7:56am

how can i give a select Query(select * from table) in stead of '@columnValue '('1,2,3')

Andrius Leonavicius 11-Jun-14 8:43am

Splitting a column value (values stored in one column => rows) and transposing columns to rows (columns => rows) are two different things. You asked how to split a column value (like 1,2,3) and I gave a solution for that. Nirav Prabtani posted a solution for transposing columns to rows. So, what do you actually want to do?

meranaamshahul 11-Jun-14 9:02am

i have a column value like (1,2,3). i need to do split it 3 rows like 1 in first row ,2 in second row, 3 in third row,

select Notes from Notes id in ([fn_CSVToTable](select notes_id from Details where details_id=1))

Andrius Leonavicius 11-Jun-14 9:33am

I just noticed that my code was not HTML encoded after pasting here (some parts were missing). I'm sorry about that. It should work now.

Andrius Leonavicius 11-Jun-14 9:39am

Please copy updated code and replace '1,2,3' with your select.

kumari567 21-Jun-16 5:58am

how to split a column in many columns in sql
like 1:1:2:3:4 in col1->1 col2->1 col3->2......

**Nirav Prabtani** · Answer 3 · 2014-06-11T01:22:00

Solution 2

try this.. unpivot for that in sql :)

SQL

SELECT * FROM (
SELECT [column1] ,[column2] ,[column3] FROM [dbo].[table1]) T
UNPIVOT ( Value FOR DataValue IN ( [column1],[column2] ,[column3] ))P

Posted 11-Jun-14 1:22am

Nirav Prabtani

Updated 11-Jun-14 1:41am

v2

Comments

meranaamshahul 11-Jun-14 7:34am

did it support in Sql server 2005

Nirav Prabtani 11-Jun-14 7:41am

try updated solution 2