xQuery LIKE-operator (starts-with)

Question

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.

score 14 · Answer 1 · answered Dec 11 '12 at 23:13

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

score 4 · Accepted Answer · answered Feb 15 '11 at 06:37

4

How about this:

select * 
from parametermaster 
where cast(ParameterName as xml).value("(en-US)[1]", "varchar(50)") LIKE 'P%'

Basically:

answered Feb 15 '11 at 06:37

marc_s

Thanks it worked but casting it in **xml** then again in **varchar** this is not good I think can you tell me any way of not double casting the values in this – Harsh Baid Feb 15 '11 at 06:50
@Harsh: yes - if you have XML, store it as datatype `XML` - then you don't need to cast around..... if you `ParameterName` column were of type `XML` in SQL Server 2005, you could save yourself that cast. BTW: the second operation is not a cast (per se) - it's a data extraction, and you just define what datatype the result will be. – marc_s Feb 15 '11 at 07:45
1

Unfortunately SQL Server 2005 supports only a small subset of the standard XQuery function library. You can't assume that because a function like starts-with is in the standard, SQL Server will support it. There's also no matches() or replace(), which means that string manipulation is very limited indeed. – Michael Kay Feb 15 '11 at 08:45

2 Answers2