Talk to the Experts

Within the value() and query() methods it is sometimes desirable to return a portion of a string.  Similar to T-SQL, there is a substring function in XQuery that provides this functionality.

In this XQuery for the Non-Expert post we’ll be looking at the syntax for this function.  Along with investigating how to implement the function in a couple of examples.

Substring Syntax

There are two sets of syntax for the substring() function.  The first method of calling allows for setting the starting location in the string and returning all characters for the rest of the string.  The second method sets a starting and ending location to return a portion of a string.

The syntax for each is the following:

fn:substring($sourceString as xs:string?, $startingLoc as as xs:decimal?)
fn:substring($sourceString as xs:string?, $startingLoc as xs:decimal?, $length as xs:decimal?)

In the syntax above the arguments are:

• $sourceString: The source string that will be manipulated
• $startingLoc: The starting point for the substring.  Values less than 1 will start at the first character.  While they start that position for returning characters the length paramter will consider their value for determining the number of characters to traverse.
• $length: The number of characters from the starting point to return.  This parameter is optional and if it isn’t supplied the function will return the remaining characters in the string.

Substring Setup

As with previous posts, let’s start with a little bit of a setup script so that we can all follow along.  This script will create the same tables and XML structures as before with a couple small changes.

Here’s the script:

IF OBJECT_ID('tempdb..#ValueExample') IS NOT NULL
    DROP TABLE #ValueExample
GO
CREATE TABLE #ValueExample
(
XMLDocument xml
)
INSERT INTO #ValueExample
VALUES ('<Building type="skyscraper">Sears Tower
<Floor level="1" Color="Lt-Blue"/>
<Floor level="2" Color="Dk-Red"/>
<Floor level="3" Color="Md-Yellow">
<Room number="3.1" />
<Room number="3.2" />
</Floor>
<Floor level="4" Color="Lt-Purple">
<Room number="4.1" />
<Room number="4.2" />
</Floor>
</Building>')
SELECT XMLDocument FROM #ValueExample

Now that you have what we need to work with we’ll go through a couple examples.

Substring Example

For the first example, we’ll use the substring() function with the starting location and leave off the optional length value.  We’ll shred the XML to the Floor element and then return the Color attribute.  From there we’ll trim off the first three characters from the Color value and return the fourth through the last characters.

The query for this will be the following:

SELECT c.query('.') AS XMLFragment
,c.value('@Color','varchar(15)') AS Color
,c.value('substring(@Color,4)','varchar(15)') AS ColorFromFour
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

With the following output:

As you can see, by looking at the ColorFromFour column the value returned from the substring function does not return the first three characters.

In the next example, we’ll add in the length parameter to the substring function.  Two columns will be returned in this example.  The first will be ColorFromZero with the substring() function starting from zero.  And then also, ColorFromOne with the substring() function starting from one.

Here’s the query:

SELECT c.query('.') AS XMLFragment
,c.value('@Color','varchar(15)') AS Color
,c.value('substring(@Color,0,3)','varchar(15)') AS ColorFromZero
,c.value('substring(@Color,1,3)','varchar(15)') AS ColorFromOne
FROM #ValueExample
CROSS APPLY XMLDocument.nodes('/Building/Floor') as t(c)

And then the output:

So in this example, the starting position of less than one doesn’t change the first character returned or generate an error.  It does though affect the value returned when the length is calculated.

Substring Wrap-Up

The substring() function in XQuery operates very similar to its T-SQL counterpart.  This post should have helped bridge the small difference with the optional parameter and demonstrated a couple ways in which this can be used.  As with the other posts, if anything in this post needs further clarity please comment below.

Related posts:

1. XQuery for the Non-Expert – Value
2. XQuery for the Non-Expert – Query
3. XQuery for the Non-Expert – Exist