COMBINE MULTIPLE SPIT VALUES IN SQL SERVER

Think if we have a string like below, it is actually a combination of name and state of some users.

Like arun is the name and kerala is the state. And the comma character is separate each user

'arun:kerala,anu:tamilnadu,sruthi:mumbai,prasanth:andra,sathya:kasmir

,bijoy:madyapredesh'

We want to split it using ‘,’ and ‘:’ so the results will be like

Arun

Kerala

Anu

Tamilnadu

Sruthi

Mumbai

Prasanth

Andra

Sathya

Kasmir

Bijoy

Madyapredesh

But what we need is we want to combine the results based on each user

Like below

NAME        STATE

-----------------------------

arun        kerala

anu         tamilnadu

sruthi      mumbai

prasanth    andra

sathya      kasmir

bijoy       madyapredesh

We can look how we can do it in SQL SERVER, one of the simplest method I have included here

IF OBJECT_ID('tempdb..#ResultTbl') IS NOT NULL

      DROP TABLE #ResultTbl

GO

CREATE TABLE #ResultTbl (

      NAME NVARCHAR(500)

      ,STATE NVARCHAR(500)

      )

DECLARE @SourceTbl TABLE (val NVARCHAR(500))

INSERT INTO @SourceTbl (val)

VALUES ('arun:kerala,anu:tamilnadu,sruthi:mumbai,prasanth:andra,sathya:kasmir,bijoy:madyapredesh');

WITH CTE

AS (

      SELECT Split.a.value('.', 'NVARCHAR(100)') AS Returndata

      FROM (

            SELECT CAST('<Mydata>' + REPLACE([val], ',', '</Mydata><Mydata>') + '</Mydata>' AS XML) AS Returndata

            FROM @SourceTbl

            ) AS A

      CROSS APPLY Returndata.nodes('/Mydata') AS Split(a)

      )

INSERT INTO #ResultTbl

SELECT SUBSTRING(Returndata, 0, CHARINDEX(':', Returndata))

      ,REVERSE(SUBSTRING(reverse(Returndata), 0, CHARINDEX(':', reverse(Returndata))))

FROM cte

SELECT *

FROM #ResultTbl

The result will be

NAME        STATE

-----------------------------

arun        kerala

anu         tamilnadu

sruthi      mumbai

prasanth    andra

sathya      kasmir

bijoy       madyapredesh

If you do not have split function then use the below split function

CREATE FUNCTION dbo.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

Querying XML data using XQuery in SQL SERVER

In many cases we have to pass xml data as parameter to our stored procedures

And it will be very easy to handle these xml data if we can query it like normal sql tables.

Here I am explaining about how to Query XML values in Tsql.

Example 1:

 Below is a sample xml data that holds the address of a user

DECLARE @xmldata XML = '<Root>

    <Address>

    <Housename>Valiyaveetil</Housename>

    <Place>Alappuzha</Place>

    <pin>688539</pin>

    </Address>

    <Address>

    <Housename>Chaithram</Housename>

    <Place>Ernakulam</Place>

      <pin>688552</pin>

    </Address>

    <Address>

    <Housename>Prasanthi</Housename>

    <Place>Kottayam</Place>

      <pin>688536</pin>

    </Address>

    <Address>

    <Housename>RajBhavanam</Housename>

    <Place>Kollam</Place>

      <pin>688529</pin>

    </Address>

    </Root>'

We can use the following query to parse the xml look like a table format.

SELECT Housename = Adr.value('(Housename)[1]', 'nvarchar(max)')

      ,Place = Adr.value('(Place)[1]', 'nvarchar(max)')

      ,Pin = Adr.value('(pin)[1]', 'nvarchar(max)')

FROM @xmldata.nodes('/Root/Address') AS Adddress(Adr)

The result of the above query will be like below.

Housename              Place              Pin

-----------------------------------------------------------

Valiyaveetil              Alappuzha      688539

Chaithram                Ernakulam      688552

Prasanthi                  Kottayam        688536

RajBhavanam          Kollam             688529

Example 2:

In  our below example we are discussing about a different scenario, here we have a common node called Common value that is common for all other nodes, state and country are common for all users. Here I am explaining  about how we can query the xml which contains common values like below .

DECLARE @xmldatas XML = '<Root>

<CommonValue>

<state>kerala</state>

<Country>India</Country>

</CommonValue>

    <Address>

    <Housename>Valiyaveetil</Housename>

    <Place>Alappuzha</Place>

    <pin>688539</pin>

    </Address>

    <Address>

    <Housename>Chaithram</Housename>

    <Place>Ernakulam</Place>

      <pin>688552</pin>

    </Address>

    <Address>

    <Housename>Prasanthi</Housename>

    <Place>Kottayam</Place>

      <pin>688536</pin>

    </Address>

    <Address>

    <Housename>RajBhavanam</Housename>

    <Place>Kollam</Place>

      <pin>688529</pin>

    </Address>

    </Root>'

The query will be like below :

SELECT Housename = Adr.value('(Housename)[1]', 'nvarchar(max)')

,Place = Adr.value('(Place)[1]', 'nvarchar(max)')

,Pin = Adr.value('(pin)[1]', 'nvarchar(max)')

,[state] = cmnvl.value('(state)[1]', 'nvarchar(max)')

,[Country] = cmnvl.value('(Country)[1]', 'nvarchar(max)')

FROM @xmldatas.nodes('/Root/Address') AS Adddress(Adr)

CROSS APPLY @xmldatas.nodes('/Root/CommonValue') AS Commonvalues(cmnvl)

It results :

Housename              Place             Pin                  state                Country

-----------------------------------------------------------------------------------------------

Valiyaveetil             Alappuzha     688539             kerala               India

Chaithram                Ernakulam     688552            kerala               India

Prasanthi                  Kottayam       688536            kerala               India

RajBhavanam          Kollam           688529            kerala               India

Split string using multiple delimiters in SQL SERVER

Many times we are Using split function in SQL SERVER for split a string using some delimiters

Example:

If we have a string like  '10:0,11:1,12:3,13:4,15:5,16:6'

And we need to split the string using comma delimiter then usually we will use some split functions

Example :

CREATE FUNCTION dbo.SplitFunction(@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   

   

Then we can call this function like

select items from dbo.SplitFunction('10:0,11:1,12:3,13:4,15:5,16:6',',')

Output :

Items

------

10

0,11

1,12

3,13

4,15

5,16

6

This is normally we all have done

But here we want to split a string using multiple delimiters

Example in our above string we want to split using “comma” and “semicolon”

We can see how we can do that.

Method 1:

This is one of the simplest methods. Use the below function it will do the task

CREATE FUNCTION dbo.MultipleDelemiterSplit

(

   @List       NVARCHAR(MAX),

   @Delemiter1 Varchar(100),

   @Delemiter2 Varchar(100)

)

RETURNS  TABLE

AS

   RETURN

   ( 

      SELECT Item = FirstSet.cnt.value('(./text())[1]', 'nvarchar(4000)')

      FROM

      (

        SELECT x = CONVERT(XML, '<cnt>'

          + REPLACE(REPLACE(@List, ISNULL(@Delemiter1,''), '</cnt><cnt>') , ISNULL(@Delemiter2,''), '</cnt><cnt>')

          + '</cnt>').query('.')

      ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)

   );

GO 

Select * From dbo.MultipleDelemiterSplit ('10:0,11:1,12:3,13:4,15:5,16:6',',',':')

OutPut:

Item

------

10

0

11

1

12

3

13

4

15

5

16

6

Method 2:

We can use REPLACE function

select REPLACE('10:0,11:1,12:3,13:4,15:5,16:6',':',',')

It will replace all occurance of semicolon with a comma,after replacing we can use normal split function for splitting

Example :

select items from dbo. SplitFunction (REPLACE('10:0,11:1,12:3,13:4,15:5,16:6',':',','),',') This will also give the same result.

Method 3:

We can use CROSS APPLY for doing the same

SELECT e.items from dbo. SplitFunction ('10:0,11:1,12:3,13:4,15:5,16:6',',') d

CROSS APPLY dbo. SplitFunction (d.items,':') e

This will also give the expected output.

Method 4  : 

Generalized approach for N number of delimiters

alter FUNCTION dbo.MultipleDelemiterSplit
(
   @List       NVARCHAR(MAX)
 
)
RETURNS  TABLE
AS

   RETURN
   (

      SELECT Item = FirstSet.cnt.value('(./text())[1]', 'nvarchar(4000)')
      FROM
      (
        SELECT x = CONVERT(XML, '<cnt>'
          + @List
          + '</cnt>').query('.')
      ) AS a CROSS APPLY x.nodes('cnt') AS FirstSet(cnt)
   );

GO

-----------------------------

DECLARE @yourDelimiterString VARCHAR(1000)

DECLARE @teststring VARCHAR(1000)

SET @yourDelimiterString = '_(){}.-+-'

SET @teststring = 'test(arun)need.to{remove}all+unwanted-'

;WITH CTE AS

(

  SELECT SUBSTRING(@yourDelimiterString, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]

  UNION ALL

  SELECT SUBSTRING(@yourDelimiterString, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1 

  FROM CTE 

  WHERE [Counter] < LEN(@yourDelimiterString)

)

SELECT @teststring = REPLACE(@teststring, CTE.[String], '</cnt><cnt>') FROM CTE

if right(@teststring,11) = '</cnt><cnt>'  

set @teststring=(select substring(@teststring, 1, (len(@teststring) - 11)))

if left(@teststring,11) = '</cnt><cnt>' 

set @teststring=Stuff(@teststring, 1, 11, '')

select * from dbo.MultipleDelemiterSplit(@teststring)

Output 

---------

item

----

test

arun

need

to

remove

all

unwanted

VARCHAR Vs NVARCHAR

Before Going for nvarchar and varchar we have to think about unicode ,non-unicode and ascii character set.

Unicode Character

Unicode's character set includes ALL human language's and written symbols.they are defined in unicode character table.

Non Unicode Character

a non unicode character is most probably a character or symbol mainly used in European countrys , simple example is English characters

ascii

There is a set of characters that are common in most generally used non-Unicode character sets and code pages. These characters are known as the ASCII characters. They include the common characters used in the English language and they occupy the first 128 code points (Hex 00-9F) in the non-Unicode code pages.

Point :

·         simply ascii is a subset of unicode

·        varchar only store non unicode characters while nvarchar can store Unicode character too

In the below example the chinese characters are unicode characters so they can  store only using nvarchar , varchar does not support those .

select '奥曼克' as testingone

Output :

testingone

------------

???

select N'奥曼克' as testingtwo

Output:

testingtwo

------------

奥曼克

declare  @test1 varchar(10) =N'奥曼克'

declare  @test2 nvarchar(10) =N'奥曼克'

declare  @test3 nvarchar(10) ='奥曼克'

select @test1

Output :

???

select @test2

Output:

奥曼克

select @test3

Output:

 ???

• N actually stands for National language character set
• N used for representing unicode characters.
• N tells to sql server  the data is unicode data and it is not a character data 

A little more information about varchar and nvarchar

select DATALENGTH(@test1) as data

Output 3 (varchar only occupy 1 byte for each character)

select DATALENGTH(@test2) as data

Output 6 (varchar only occupy 2 byte for each character)

so the maximum userdefined size for varchar will be 8000 and nvarchar will be 4000

declare @a nvarchar(4001)

will give error(maximum limit is 4000 characters)

declare @aa varchar(4001) this will  works fine