who to make where clause case insensitive

, ,
1

Hello again,

Is there a way to make an expression in a where clause case insensitive? I currently have this where clause:

where $settings/@UserId='Johnny'

Unfortunately, the case of the UserId attribute is unpredicatble. Is there an Xquery expression operator I can use to make this case insensitive?

Thanks,
Allen

2

You can use tf:contains-text($settings/@UserId,“John”). However, this would match on “John”, “Johnson” and “Johnny”, so the lower-case (or upper-case) function might work better:

where lower-case($settings/@UserID) = lower-case("Johnny")

See the documentation “XQuery Reference Guide” for more functions and details.

3

A good pragmatic approach. For the purists:

you can influence Tamino’s behaviour also by specifying a collation, e.g. in the prologue of an XQuery:

declare default collation “collation?language=…”

or in the comparison
where compare($settings/@UserId, ‘Johnny’, “collation?..”) = 0

where you can also specify to ignore case

4

This is a 4.2.1 database so using the lower-case() function did not work. I did not see this function described in the 4.2.1 documentation.

I tried the following which did not work either:

where compare($settings/@UserId,'Johnny','collation?caseLevel=true') = 0

Any ideas why the above did not work? I did not really understand the documentation on caseLevel and I’m not sure it’s really what I need to use.

Thanks,
Allen

5

In 4.2, you can use:
declare default collation “collation?strength=secondary”;
“X” = “x”

or

compare(“X”, “x”, “collation?strength=secondary”)

But it might be a good idea to upgrade to 4.4!

Regards

Harald

6

ok, well I have to agree with Alan’s problem…I wouldn’t have found this to perform a case-insensitive search either. Even after reading it through, I’d be hard-pressed to know that was what this collation?strength was for.

Be that as may, I’m not sure if I’ve understood the suggestion - if I try the two forms on my Tamino 4.4 database, I get completely different results:

declare default collation "collation?strength=secondary"
for $r in input()/Role
where $r/@RoleName = "purchasing agent"
return $r

retrieves the expected single document

<Role MDR_JobFunction="002" RoleID="1" RoleName="Purchasing Agent"/>

but

for $r in input()/Role
where compare($r/@RoleName, "purchasing agent","collation?strength=secondary")
return $r

retrieves a set of documents that don’t match my selection, such as

<Role MDR_JobFunction="205" RoleID="2" RoleName="Middle/Junior High Princi"/>
<Role MDR_JobFunction="301" RoleID="3" RoleName="Math Dept Chairperson"/>
<Role MDR_JobFunction="321" RoleID="4" RoleName="Science Dept Chairperson"/>

am I using the compare function in the second example incorrectly?

7

Sorry, I was too short. The compare function returns an integer (0 if the values are equal). So in the form you used it, you selected all elements where the value was non-0 (and these are the elements you did not want to see :frowning: ). If you change to


for $r in input()/Role
where compare($r/@RoleName, "purchasing agent","collation?strength=secondary")=0
return $r

you should get the expected result.

Regards

Harald

8

It took me a while to get back to this but I wanted to let everyone know that Dr. Sch