/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Thursday, November 16, 2017

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 »

Monday, February 27, 2017

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 »

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago