Bobnottm Bobnottm - 3 months ago 7
SQL Question

How to separate out GUIDs so as to perform a join

I have output in SSRS when there is a massive string of GUIDs that form the parameters:


FinancialYear=47faabe1-f6aa-e411-941e-00155d010d10&

FinancialPeriod=4dfaabe1-f6aa-e411-941e-00155d010d10&

Branch=eefb631b-4e1b-e611-9436-00155d010d10&

Branch=369d0919-edaa-e411-941e-00155d010d10&

Branch=e9a7dc75-f9aa-e411-941e-00155d010d10&

Branch=cb3a3c67-f9aa-e411-941e-00155d010d10&

Department=00000000-0000-0000-0000-000000000000&

Department=adb0f49a-4f1b-e611-9436-00155d010d10&

Department=487c64ae-4f1b-e611-9436-00155d010d10&

Department=4c9b0919-edaa-e411-941e-00155d010d10


Does anyone know a way to separate these out so as to do a join and obtain their names?

Ideally the output would look something like:

FinancialYear 2016
FinancialPeriod 12
Branch London
Branch Main
Branch Manchester
Branch Leeds
Department Mortgages
Department Loans
Department Insurance
Department Life Assurance

Answer

With the aid of a parser

Declare @String varchar(max) = 'FinancialYear=47faabe1-f6aa-e411-941e-00155d010d10&FinancialPeriod=4dfaabe1-f6aa-e411-941e-00155d010d10&Branch=eefb631b-4e1b-e611-9436-00155d010d10&Branch=369d0919-edaa-e411-941e-00155d010d10&Branch=e9a7dc75-f9aa-e411-941e-00155d010d10&Branch=cb3a3c67-f9aa-e411-941e-00155d010d10&Department=00000000-0000-0000-0000-000000000000&Department=adb0f49a-4f1b-e611-9436-00155d010d10&Department=487c64ae-4f1b-e611-9436-00155d010d10&Department=4c9b0919-edaa-e411-941e-00155d010d10'
Select Seq   =Key_PS
      ,Item  =Substring(Key_Value,1,charindex('=',Key_Value)-1)
      ,Value =Substring(Key_Value,  charindex('=',Key_Value)+1,100)
 From [dbo].[udf-Str-Parse](@String,'&')

Returns

Seq Item            Value
1   FinancialYear   47faabe1-f6aa-e411-941e-00155d010d10
2   FinancialPeriod 4dfaabe1-f6aa-e411-941e-00155d010d10
3   Branch          eefb631b-4e1b-e611-9436-00155d010d10
4   Branch          369d0919-edaa-e411-941e-00155d010d10
5   Branch          e9a7dc75-f9aa-e411-941e-00155d010d10
6   Branch          cb3a3c67-f9aa-e411-941e-00155d010d10
7   Department      00000000-0000-0000-0000-000000000000
8   Department      adb0f49a-4f1b-e611-9436-00155d010d10
9   Department      487c64ae-4f1b-e611-9436-00155d010d10
10  Department      4c9b0919-edaa-e411-941e-00155d010d10

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End