Subscribe to this thread
Home - General / All posts - Text Selection templates
dyalsjas95 post(s)
#07-Feb-18 20:34

The options for the text selection templates seem inconsistent. Is there a design tenet that I'm missing?

Why would I use the Text Contains selection template instead of the Text Contains, Intl template?

Selection Template: Options

Text Contains: Value and Search for

Text Contains Intl: Value, Search for, Language, Ignore case, Ignore accent, and Ignore symbols

Text Ends with: Value, and Search for

Text Ends with, Intl: Value, Search for, Language, Ignore case, Ignore accent, and Ignore symbols

Text Matches Regexp: Value, Pattern, and Ignore case

Text Sounds like: Value, Compare to

Text Starts with: Value and Search for

Text Starts with, Intl: Value, Search for, Language, Ignore case, Ignore accent, and Ignore symbols

tjhb
8,009 post(s)
#07-Feb-18 22:37

In a nutshell, the *Intl template versions use *Collate SQL string functions, meaning that they apply locale-specific comparison rules. The plain versions (non-Intl, non-Collate) do not, instead treating all strings as exact literals.

See the COLLATE topic in help, and the Collate() function under SQL Functions.

You can inspect the SQL used by each Transform template by pressing the Edit Query button.

(There currently seems to be a strange quirk, whereby the templates write SQL using 'NOT false' to mean 'TRUE'. I don't think that matters.)

dyalsjas95 post(s)
#07-Feb-18 23:08

http://www.georeference.org/forum/t141598#141609

The linked forum posting expands on why I asked.

Thanks for the clarification.

Is there a way to add a non-case sensitive option to the plain version?

tjhb
8,009 post(s)
#07-Feb-18 23:23

Is there a way to add a non-case sensitive option to the plain version?

Yes, by enforcing case before comparison.

E.g.

SELECT [title_name]

FROM [titles]

WHERE StringContains(StringToLowerCase([title_name]), 'mo')

;

(or the same using StringToUpperCase() and 'MO').

(Or similar using LIKE.)

dyalsjas95 post(s)
#07-Feb-18 23:35

Thanks,

I actually meant to ask if a check block to specify non-case sensitive could be added to the template; like there is for the Intl templates. I'll look at submitting a feature request.

I'll be using your query as well.

tjhb
8,009 post(s)
#07-Feb-18 23:37

Good idea--but there may be speed implications for large datasets (which the template could nevertheless mitigate by being clever). See comments below.

dyalsjas95 post(s)
#08-Feb-18 00:39

I've just submitted a suggestion to add an "Ignore case" check box selection to the "plain" Text selection templates.

tjhb
8,009 post(s)
#07-Feb-18 23:35

By the way, I think that a *Collate comparison (or *Intl template) should be significantly faster, if there is a BTREE* index (i.e. BTREE, BTREEDUP, BTREENULL or BTREEDUPNULL) on the field(s) being tested.

That is because applying StringToLowerCase() or StringToUpperCase() will mean the index won't be used.

[Actually, this shows that I don't fully appreciate the importance of COLLATE options on indexes. We can specify that indexes themselves disregard case (and accents...). I haven't paid any attention to this until now--but should. Thanks for the prompt!]

If there is no index then there shouldn't be much difference.

(When you really need speed it will be worth planning around these factors, by enforcing case in advance, then adding an index on the result. It only matters given lots of data.)

Dimitri


4,843 post(s)
#08-Feb-18 09:29

Why would I use the Text Contains selection template instead of the Text Contains, Intl template?

That's discussed in the Contents - Select topic. Use the Text Contains, Intl template when you want the additional options it provides. The design tenet is to provide a simpler command for simpler needs.

I actually meant to ask if a check block to specify non-case sensitive could be added to the template; like there is for the Intl templates.

Well, that is one of the additional options that Text Contains, Intl provides. Why not just use that ? It has that checkbox already. Works perfectly.

Personally, I think it would be way more convenient to get rid of using single quotes when specifying the text to search for.

dyalsjas95 post(s)
#09-Feb-18 02:10

I was able to get the correct selection using the Text Contains, Intl template.

When I copied the query to examine it in a a command windows, I found that the Text Contains, Intl template used the COLLATE SQL function (as Tim described in his post).

The exercise from the Fehily text listing was to recognize that you could use the UPPER case function (StringToUpperCase in Manifold) to examine all text in a field(column) as if it was in UPPER case, and select records using LIKE, an uppercase text string, and wildcard characters.

From the perspective of just using what works; the COLLATE function called by the Text Contains, Intl template works (as you said) perfectly, but it wasn't the topic Fehily was covering in the text.

In that I'm trying to learn SQL programming using Manifold, I'm attempting to replicate the Fehily text listing exercises as closely as I can.

Ignore case seems to be an appropriate option when one is using any of the Text templates. The Manifold development team can determine if an Ignore case option can be implemented effectively for the Text templates that don't call the COLLATE SQL function.

It took several hours away for my brain to make the connections that let me understand this exercise. Now it seems obvious. I wouldn't have made that connection if I hadn't gotten to success using the StringToUpperCase function.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.