xQuery LIKE-operator (starts-with)

xml sql-server-2005 where-clause sql-like xquery-sql

Harsh Baid · Feb 15, 2011 · Viewed 8.8k times · Source

Value in the field is as follows

<en-US>Parameter23</en-US>
<it-IT>Parameter</it-IT>

SQL query is

select * 
from parametermaster 
where cast(ParameterName as xml).exist('en-US/text()[contains(.,"P")]') = 1

And I am trying to it as

select * 
from parametermaster 
where cast(ParameterName as xml).exist('en-US/text()[starts-with(.,"P")]') = 1

Is is giving error as

Msg 2395, Level 16, State 1, Line 1
XQuery [exist()]: There is no function '{http://www.w3.org/2004/07/xpath-functions}:starts-with()'

Can anyone help me please, I want to create LIKE operator feeling in SQL 2005 XQuery. And I am an newbie in XQuery.

Answer

starts-with()/ends-with() can be substituted by combinations of substring() and string-length() functions:

select * 
from parametermaster 
where cast(ParameterName as xml).exist('en-US/text()[substring(., 1, string-length("P")) = "P"]') = 1

In general starts-with(a, b) is equivalent to

substring(a, 1, string-length(b)) = b

and ends-with(a, b) is equivalent to

substring(a, string-length(a) - string-length(b)) = b