Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

upper-case Function (XQuery)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

This function converts each character in $arg to its upper case equivalent. The Microsoft Windows binary case conversion for Unicode code points specifies how characters are converted to upper case. This standard is different than the mapping for Unicode standard code point standard.

fn:upper-case($arg as xs:string?) as xs:string




The string value to be converted to upper case.

If the value of $arg is empty, a zero length string is returned.

A. Changing a string to upper case

The following example changes the input string 'abcDEF!@4' to upper case.

DECLARE @x xml = N'abcDEF!@4';
SELECT @x.value('fn:upper-case(/text()[1])', 'nvarchar(10)');

B. Search for a Specific Character String

This example shows how to use the upper-case function to perform a case-insensitive search.

USE AdventureWorks
--WITH XMLNAMESPACES clause specifies the namespace prefix
--to use. 
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS pd)
--The XQuery contains() function is used to determine whether
--any of the text nodes below the <Summary> element contain
--the word 'frame'. The upper-case() function is used to make
--the search case-insensitive.

SELECT ProductModelID, CatalogDescription.query('
         { /pd:ProductDescription/@ProductModelID }
         { /pd:ProductDescription/pd:Summary }
 ') as Result
FROM Production.ProductModel
where CatalogDescription.exist('
          contains(upper-case(.), "FRAME")]')  = 1

Here is the result set.

ProductModelID Result

-------------- ---------

19 <Prod ProductModelID="19">

<pd:Summary xmlns:pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">

<p1:p xmlns:p1="http://www.w3.org/1999/xhtml">Our top-of-the-line competition mountain bike.

Performance-enhancing options include the innovative HL Frame,

super-smooth front suspension, and traction for all terrain.




25 <Prod ProductModelID="25">

<pd:Summary xmlns:pd="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription">

<p1:p xmlns:p1="http://www.w3.org/1999/xhtml">This bike is ridden by race winners. Developed with the

Adventure Works Cycles professional race team, it has a extremely light

heat-treated aluminum frame, and steering that allows precision control.




Community Additions

© 2015 Microsoft