Find values within braces using T-SQL and XQuery

Introduction

Suppose we have a text like "Hello !!! We are [DNF] Dotnetfunda.Please use [F5] the site properly to get more information about [MS] Microsoft Technologies".And the objective is to figure out the value within the brackets ([ and ]).In this article, we will look into one of the techniques as how can we achieve this.

Environment Setup

DECLARE @str AS VARCHAR(500)
SET @str= 'Hello !!! We are [DNF] Dotnetfunda.Please use [F5] the site properly to get more information about [MS] Microsoft Technologies'

Using the code

The first thing that we will do is to replace the '[' with say '|-' and ']' with say '-|' symbols as shown under

SELECT ReplaceStr =REPLACE(REPLACE(@str,'[','|-'),']','-|')

This will give us the output as

The next step is to Replace the '|' delimiter/separator symbols with </X><X> as under

DECLARE 
	@str AS VARCHAR(500)
	,@xml AS XML
	,@delimiter AS VARCHAR(1)

--Set the original string
SET @str= 'Hello !!! We are [DNF] Dotnetfunda.Please use [F5] the site properly to get more information about [MS] Microsoft Technologies'

--Set the delimiter
SET @delimiter ='|'

-- Replace the [ and ] with '|-' and '-|' respectively
SELECT @str=REPLACE(REPLACE(@str,'[','|-'),']','-|')

-- Replace the '|' symbols with 
SET @xml = CAST((''+REPLACE(@str,@delimiter ,'')+'') AS XML)

SELECT @xml

The next step is to query the XML nodes using XQuery as shown under

--Query the XML using XQuery
SELECT 
	N.value('.', 'varchar(500)') AS QueryResult
FROM @xml.nodes('X') AS T(N)

As interesting pattern has cropped up. The values within the original braces are now appended with - and -. The final task is to pickup those using the pattern

SELECT 
	N.value('.', 'varchar(500)') AS QueryResult	
FROM @xml.nodes('X') AS T(N)
WHERE PATINDEX('%[-]%', N.value('.', 'varchar(100)')) = 1

As can be figure out by using the PATINDEX function we are able to figure out the patterns of our choice.

As a final step, replace the '-' with blank

SELECT 
	REPLACE(N.value('.', 'varchar(500)'),'-','') AS QueryResult	
FROM @xml.nodes('X') AS T(N)
WHERE PATINDEX('%[-]%', N.value('.', 'varchar(100)')) = 1

The final query is as under

DECLARE 
	@str AS VARCHAR(500)
	,@xml AS XML
	,@delimiter AS VARCHAR(1)

--Set the original string
SET @str= 'Hello !!! We are [DNF] Dotnetfunda.Please use [F5] the site properly to get more information about [MS] Microsoft Technologies'

--Set the delimiter
SET @delimiter ='|'

-- Replace the [ and ] with '|-' and '-|' respectively
SELECT @str=REPLACE(REPLACE(@str,'[','|-'),']','-|')

-- Replace the '|' symbols with </X><X>
SET @xml = CAST(('<X>'+REPLACE(@str,@delimiter ,'</X><X>')+'</X>') AS XML)

--Query the XML using XQuery and apply the filter using Patindex
SELECT 
	REPLACE(N.value('.', 'varchar(500)'),'-','') AS QueryResult	
FROM @xml.nodes('X') AS T(N)
WHERE PATINDEX('%[-]%', N.value('.', 'varchar(100)')) = 1

Reference

PATINDEX

XQuery Language Reference

Conclusion

This article has shown us a technique of filtering the records using PATINDEX and query XML nodes using XQuery.Hope this will be useful.Thanks for reading.Zipped file is attached herewith.