-5

Here is my string/column value:

ReferenceId=678&MobileNo=226789&Pnumber=9878&SIB=121314&.........&PIN=34&....

There are so many fields. I am interested in above mentioned field. How to separate/fetch value of them in SQL Server 2012? The string_split() function is not supported in 2012.

Dale K
  • 25,246
  • 15
  • 42
  • 71
smita
  • 1
  • 2

1 Answers1

0

create the UFN_STRING_SPLIT function suggested by @Dave here link

create FUNCTION [dbo].[UFN_STRING_SPLIT]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(max)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a 
      CROSS APPLY x.nodes('i') AS y(i)
   );

test your code:

select
    Left(Item, charindex('=', Item, 0) - 1) as FielName,
    right(Item, len(Item) - charindex('=', Item, 0)) as [Value]
from
    dbo.UFN_STRING_SPLIT('ReferenceId=678&MobileNo=226789&Pnumber=9878&SIB=121314', '&')
Eliseu Marcos
  • 311
  • 2
  • 6
  • Hello Eliseu,that help me lot.But how find whose value is what.Like 678 is ReferenceId and Pnumber is 9878.How to store in variable.Because want to use this in further processing – smita Aug 11 '20 at 18:31
  • Hi @smita! I just updated the answer, in the part where the `UFN_STRING_SPLIT` function is called, now the query returns `FielName` and `Value`. – Eliseu Marcos Aug 11 '20 at 20:00