Tonight I was monitoring the #sqlhelp hashtag on Twitter for a response to a question I asked when Randy Knight (Twitter) asked a question about using SUBSTRING in FLOWR statements with XML.
#sqlhelp Is there a way to do a SQL Type "LIKE" or "SUBSTRING" in the where clause of FLWOR statement? Need to evaluate just first n chars.
By the time I posted a response, Randy had figured out how to use the contains() function to solve his problem, but I am going to blog this because I am sure that I will need this at some point in the future, and it makes for a good blog post anyway.
Using an example from the Books Online topic for FLOWR, I mocked up the following example showing how to use substring() in the where clause of a FLOWR statement.
declare @x xml
set @x='<ManuInstructions ProductModelID="1" ProductModelName="SomeBike" >
<Location LocationID="L1" >
<Step>Manu step 1 at Loc 1</Step>
<Step>Manu step 2 at Loc 1</Step>
<Step>Manu step 3 at Loc 1</Step>
<Location LocationID="L2" >
<Step>Manu step 1 at Loc 2</Step>
<Step>Manu step 2 at Loc 2</Step>
<Step>Manu step 3 at Loc 2</Step>
for $step in /ManuInstructions/Location/Step
where substring(string($step), 11, 1) = "2"
This outputs only a single node value where the substring() function evaluation matches the string value of 2.