Find values within braces using T-SQL and XQuery

Rajnilari2015
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 803 red flag

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 using T-SQL and XQuery.


 Download source code for 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.

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)