substring Function (XQuery)

substring Function (XQuery)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

substring Function (XQuery)

Returns part of the value of $sourceString, starting at the position indicated by the value of $startingLoc, and continues for the number of characters indicated by the value of $length.

Syntax

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

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

Arguments

$sourceString: Source string.

$startingLoc: Starting point in the source string from which the substring starts. If this value is negative or 0, only those characters in positions greater than zero are returned. If it is greater than the length of the $sourceString, the zero-length string is returned.

$length: [optional] Number of characters to retrieve. If not specified, it returns all the characters from the location specified in $startingLoc up to the end of string.

Remarks

The three-argument version of the function returns the characters in $sourceString whose position $p obeys:

fn:round($startingLoc) <= $p < fn:round($startingLoc) + fn:round($length)

The value of $length can be greater than the number of characters in the value of $sourceString following the start position. In this case, the substring returns the characters up to the end of $sourceString.

The first character of a string is located at position 1.

If the value of $sourceString is the empty sequence, it is handled as the zero-length string. Otherwise, if either $startingLoc or $length is the empty sequence, the empty sequence is returned.

Examples

This topic provides XQuery examples against XML instances stored in various xml type columns in the AdventureWorks database. For an overview of each of these columns, see xml Data Type Representation in the AdventureWorks Database.

A. Using the substring() XQuery function to retrieve partial summary product-model descriptions

The query retrieves the first 50 characters of the text that describes the product model, the <Summary> element in the document.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
SELECT ProductModelID, CatalogDescription.query('
    <Prod>{ substring(string((/pd:ProductDescription/pd:Summary)[1]), 1, 50) }</Prod>
 ') as Result
FROM Production.ProductModel
where CatalogDescription.exist('/pd:ProductDescription')  = 1

Note the following from the previous query:

The string() function returns the string value of the<Summary> element. This function is used, because the <Summary> element contains both the text and subelements (html formatting elements), and because you will skip these elements and retrieve all the text.
The substring() function retrieves the first 50 characters from the string value retrieved by the string().

This is a partial result:

ProductModelID Result
-------------- ----------------------------------------------------
19      <Prod>Our top-of-the-line competition mountain bike.</Prod> 
23      <Prod>Suitable for any type of riding, on or off-roa</Prod>
...

Implementation Limitations

These are the limitations:

SQL Server requires the $startingLoc and $length parameters to be of type xs:decimal instead of xs:double.
SQL Server allows $startingLoc and $length to be the empty sequence, because the empty sequence is a possible value as a result of dynamic errors being mapped to ().
Unicode UTF-16 surrogate pairs are counted as two characters instead of one.

Reference

XQuery Functions against the xml Data Type

Help and Information

Getting SQL Server 2008 Assistance