How to split comma separated value in sql server

How to split comma separated value in SQL server :

----- Approach 1:  Common Table Expression (CTE) ----- Lets call this function as Split2.  here we are using
CREATE FUNCTION dbo.Split2 ( @strString varchar(4000))
RETURNS  @Result TABLE(Value BIGINT)
AS
begin
    WITH StrCTE(start, stop) AS
    (
      SELECT  1, CHARINDEX(',' , @strString )
      UNION ALL
      SELECT  stop + 1, CHARINDEX(',' ,@strString  , stop + 1)
      FROM StrCTE
      WHERE stop > 0
    )   
    insert into @Result
    SELECT   SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue
    FROM StrCTE  
    return
end
GO

----- Approach 2:  XML (surprise) ----- XML could be applied to do some type of string parsing (see this) Let’s call this function as Split3.
CREATE FUNCTION dbo.Split3 ( @strString varchar(4000))
RETURNS  @Result TABLE(Value BIGINT)
AS
BEGIN
      DECLARE @x XML
      SELECT @x = CAST(''+ REPLACE(@strString,',','')+ '' AS XML)    
      INSERT INTO @Result           
      SELECT t.value('.', 'int') AS inVal
      FROM @x.nodes('/A') AS x(t)
    RETURN
END 
GO

----- Approach 3: Classic TSQL Way ----- This approach is slightly unusual but very effective. this needs you to create a table of sequential numbers called a Tally Table.
SELECT TOP 11000 --equates to more than 30 years of dates     
IDENTITY(INT,1,1) AS N 
INTO dbo.Tally 
FROM Master.dbo.SysColumns sc1,     
Master.dbo.SysColumns sc2

----- Lets index the table for better performance. -----
ALTER TABLE dbo.Tally 
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
WITH FILLFACTOR = 100

----- Finally out Split4 function. -----
CREATE FUNCTION dbo.Split4 ( @strString varchar(4000))
RETURNS  @Result TABLE(Value BIGINT)
AS
BEGIN
      SET @strString = ','+@strString +','
      INSERT INTO @t  (Value)
      SELECT SUBSTRING(@strString,N+1,CHARINDEX(',',@strString,N+1)-N-1) 
      FROM dbo.Tally
      WHERE N < LEN(@strString) 
      AND SUBSTRING(@strString,N,1) = ',' --Notice how we find the comma
      RETURN
END 
GO

----- so I changed the code slightly to test out XML function directly and copied data ia a temporary table -----
--declare @str varchar(4000) = '1,2,3,4,5,6'
declare @str varchar(4000) = 'A,B,C,D,E,F'
declare @xml XML
select @xml = cast(''+REPLACE(@str,',','')+'' as XML)
select * into tempTable  from  (select t.value('.', 'VARCHAR') as inVal from @xml.nodes('/A') as x(t)) as tbl
select * from tempTable

----- so I changed the code slightly to test out XML function directly and copied data using Table Variable Parameter -----
--declare @str varchar(4000) = '1,2,3,4,5,6'
declare @temp table(VALUE nvarchar(100))
declare @str varchar(4000) = 'A,B,C,D,E,F'
declare @xml XML
select @xml = cast(''+REPLACE(@str,',','')+'' as XML)
insert into @temp select t.value('.', 'VARCHAR') as inVal from @xml.nodes('/A') as x(t)
select * from @temp

Read More »

How to get odd/even value from array and display in table using Javascript

var dataArr = ["1","Maths","2","Science"];

Add a bare bones table to a div.

$("#subjectTable").append('
');

For each table row loop over the array in steps of 2 (i+=2). oddPositionVal is the first element in the step, evenPositionVal is the second element.

Build the row HTML and then append it to the table.

for (var i = 0, l = dataArr.length; i < l; i+=2) {

  var oddPositionVal = dataArr[i];

  var evenPositionVal = dataArr[i + 1];

  var rowhtml = ' ' + oddPositionVal + '' + evenPositionVal + ' ';

  $('#table').append(rowhtml);

}

http://stackoverflow.com/questions/25761856/how-to-get-odd-even-value-from-array-and-display-in-table-using-javascript 

Read More »