How to split values in sql? table column im having (1,2,3,4)

Question

im having table coloumn like 1,2,3,4 .i want to split those values and inserted in another table?

Vivek Kumar · Answer

Hi Siva, You can take help from function. it will help you in any cases. Below are the simple steps: Step 1: run the below function in your database SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[SplitString] ( @Input NVARCHAR(MAX), @Character CHAR(1) ) RETURNS @Output TABLE ( Item NVARCHAR(1000) ) AS BEGIN DECLARE @StartIndex INT, @EndIndex INT SET @StartIndex = 1 IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) @Character BEGIN SET @Input = @Input + @Character END WHILE CHARINDEX(@Character, @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@Character, @Input) INSERT INTO @Output(Item) SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1) SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input)) END RETURN END step 2: for testing the function create the table named as splitTest as below CREATE TABLE splitTest(name nvarchar(max)) step 3: test the result as below declare @value nvarchar(max)='siva,nathan,vivek'; insert into splitTest select Item from SplitString(@value,',') select * from splitTest ------------------------------------------------------- If this post helps you, mark it as answer

Manish Kumar · Answer

Hi, Try this ..... declare @strvalue nvarchar(1000),@seprator nvarchar(20),@idlen int set @strvalue='1,2,3,4,5,6,7,8,9,0' --select SUBSTRING(@strvalue,1,2-1) set @seprator=',' set @idlen=0 declare @tbl table (value nvarchar(1000)) while (select CHARINDEX(@seprator,@strvalue))>0 begin set @idlen=CHARINDEX(@seprator,@strvalue) if @idlen begin break end insert into @tbl select SUBSTRING(@strvalue,1,@idlen-1) set @strvalue=SUBSTRING(@strvalue,@idlen+1,LEN(@strvalue)) end insert into @tbl select @strvalue select * from @tbl

siva nathan · Answer

i can't understand that query. explain me a simple query

Vivek Kumar · Answer

Hi Siva, Please look into below links. http://blog.sqlauthority.com/2015/04/21/sql-server-split-comma-separated-list-without-using-a-function/ http://www.aspsnippets.com/Articles/Split-and-convert-Comma-Separated-Delimited-String-to-Table-in-SQL-Server.aspx --------------------------------------------------------- If this post helps you, mark it as answer

How to split values in sql? table column im having (1,2,3,4)

Answers (4)