Mohammad Abu Saloum Mohammad Abu Saloum - 1 month ago 7
SQL Question

How get only ID from long string in SQL Server

in the below string, I need your help in order to know how I can get or select only

GUID
(
NVARCHAR(36)
) using a T-SQL query?

N'yes#red#A5257199-8B09-44F1-8073-C5D5F02126F9#No#fuchsia#A5A5B2F2-1B87-4B0E-85B6-16A287814574#'

Answer

If you REALLY don't want to use the function as suggested by Prdp (which is honestly the right way to go), you can do something like below

Declare @YourTable table (ID int,SomeString varchar(max))
Insert into @YourTable values
(1,'yes#red#A5257199-8B09-44F1-8073-C5D5F02126F9#No#fuchsia#A5A5B2F2-1B87-4B0E-85B6-16A287814574#')

Select A.ID
      ,GUIDs = B.RetVal 
 From @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From (Select x = Cast('<x>'+ Replace(A.SomeString,'#','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)

             ) B Where Len(RetVal)=36

Returns

ID  GUIDs
1   A5257199-8B09-44F1-8073-C5D5F02126F9
1   A5A5B2F2-1B87-4B0E-85B6-16A287814574