New post
Question
· Aug 23, 2016

SQL query case insensitive

#SQL #Caché

I try it with the COLLATE instruction, but doesn't work.

Any ideas ?

Tkanks.

Discussion (3)0
Log in or sign up to continue
Brendan Bannon · Aug 23, 2016

Can you give more details on what you are trying to do.?  Properties defined as %String have a default collation of SQLUPPER and any comparisons against those fields will be case insensitive.  

The only time this is not true if it the condition is against part of the IDKey.   The IDKey always uses EXACT collation.

You can force any collation you want by including it in the query like this:

SELECT Name FROM Sample.Person WHERE %UPPER(Name) LIKE 'A%'

2 0
Brendan Bannon · Aug 23, 2016

Man I need to learn how to read :(

So you are asking about COLLATE, not COLLATION.  Let me try again.

In Cache you can change the way data is sorted and other regional things (date and number formats for example) by using the National Language Support (NLS).  By selecting one of the available locales you can control the sort order of your data.

You can configure NLS setting via classes:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

or from the portal:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

There is also an article that give some examples of working with NLS:  https://community.intersystems.com/post/collations-cach%C3%A9

I hope this post is a little more in line with what you were looking for.

3 0