List Regex Matches

Question

CREATE TABLE [dbo].[RTbl](
	[rowguid] [nvarchar](100) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[RTbl] ([rowguid]) VALUES (N'AF.17.48A3.8D448.4')
INSERT [dbo].[RTbl] ([rowguid]) VALUES (N'D9.8AA8.47CC9.AFF7.C')
INSERT [dbo].[RTbl] ([rowguid]) VALUES (N'E5E.0828.43S.A33B.6F38.13')
INSERT [dbo].[RTbl] ([rowguid]) VALUES (N'06.38B3.34.80W5B.F4B')
INSERT [dbo].[RTbl] ([rowguid]) VALUES (N'4.AE3B.4C')
GO

declare @pat1 varchar(100) = '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%'
declare @pat2 varchar(100) = '%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%'

;with a as (
select [rowguid]
, case when patindex(@pat1, [rowguid]) > 0 then substring([rowguid], patindex(@pat1, [rowguid]), 4) end as pat1
, case when patindex(@pat2, [rowguid]) > 0 then substring([rowguid], patindex(@pat2, [rowguid]), 5) end as pat2
from [dbo].[RTbl]
)
select [rowguid],
case when len(pat1) > 0 and len(pat2) > 0 then pat1 + ';' + pat2 
		when len(pat1) > 0 then pat1
		when len(pat2) > 0 then pat2
end as val
from a

Here are the results.

I need the val column to look like this.

val

48A3;8D448

8AA8;47CC9;AFF7

0828;A33B;6F38

38B3;80W5B

AE3B

Answer 1

Hi @CB Weiner

You could also create a User-defined Function.

Please check this:

GO
Create or Alter function UDF_ExtractElements_1
(  
  @input nvarchar(100)
)  
Returns varchar(100)  
As  
Begin  
  Declare @numberIndex_5 int = Patindex('%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%', @input)  
  Declare @numberIndex_4 int = Patindex('%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%', @input)  
  Declare @Output varchar(100) =''
  Begin  
    While @numberIndex_5 > 0 or @numberIndex_4 > 0
    Begin  
	  Set @Output = @Output+
	                Case When @numberIndex_4 = 0 Then SUBSTRING(@input,@numberIndex_5,5)+';' 
						 When @numberIndex_5 = 0 Then SUBSTRING(@input,@numberIndex_4,4)+';'
						 When @numberIndex_4 <= @numberIndex_5 Then SUBSTRING(@input,@numberIndex_4,4)+';'
						 When @numberIndex_4 > @numberIndex_5 Then SUBSTRING(@input,@numberIndex_5,5)+';' 
						 End
	  Set @input = Case When @numberIndex_4 = 0 Then STUFF(@input,@numberIndex_5,5,'')
						When @numberIndex_5 = 0 Then STUFF(@input,@numberIndex_4,4,'')
						When @numberIndex_4 <= @numberIndex_5 Then STUFF(@input,@numberIndex_4,4,'') 
						When @numberIndex_4 > @numberIndex_5 Then STUFF(@input,@numberIndex_5,5,'') 
	                    End
      Set @numberIndex_5 = Patindex('%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%', @input) 
	  Set @numberIndex_4 = Patindex('%[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]%', @input)
    End  
  End  
  Return @Output
End
GO

SELECT [rowguid],LEFT(dbo.UDF_ExtractElements_1([rowguid]),LEN(dbo.UDF_ExtractElements_1([rowguid]))-1) AS VAL
FROM [RTbl]

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @CB Weiner

If I understood your issue correctly, you might use STRING_SPLIT function first to split val into single values. Then filter out values of length 4 or 5. Last, use STRING_AGG or XML to combined values in one line for each rowguid by ';'.

Check this query:

--Use STRING_AGG for SQL Server 2017 (14.x) and later
SELECT rowguid,STRING_AGG(S.value,';') AS val
FROM [RTbl] CROSS APPLY STRING_SPLIT(rowguid,'.')S
WHERE LEN(S.value)=4 OR LEN(S.value)=5
GROUP BY rowguid

--Use XML method instead if your SQL server donot support STRING_AGG
SELECT rowguid,
       STUFF((SELECT ';' + S.value FROM [RTbl] CROSS APPLY STRING_SPLIT(rowguid,'.')S
              WHERE (LEN(S.value)=4 OR LEN(S.value)=5) AND rowguid=R.rowguid 
			  FOR XML PATH(''))
		    ,1,1,'') AS val
FROM [RTbl] R
GROUP BY rowguid

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Check a query:

select rowguid, string_agg(t.value, ';') as val2
from RTbl
cross apply string_split(rowguid, '.') t
where t.value like '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'
or t.value like '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'
group by rowguid