New post
Question
· Mar 18, 2021

How to make a select return accented characters

#SQL #Caché

PORTUGUESE

Olá a todos!

Estou com dificuldades de fazer meu select para retornar palavras que contém acentos.

Na minha tabela TESTE, por exemplo, tenho palavras no campo NOME como Fábio e Fabio, porém se eu coloco a instrução:

SELECT * FROM TESTE
WHERE nome LIKE 'FÁBIO'

a instrução só traz FÁBIO.

Como eu faço para trazer todas as palavras: FÁBIO, FABIO, FABÍO, FABIÓ, FÁBÍÓ, FÂBIO, etc...

Conto com sua ajuda!

 

ENGLISH

Hello everyone!

I'm having trouble making my select to return words that contain accents.

In my TEST table, for example, I have words in the NAME field like Fábio and Fabio, but if I put the instruction:

SELECT * FROM TEST
WHERE name LIKE 'FÁBIO'

the instruction only brings FÁBIO.

How do I bring all the words: FÁBIO, FABIO, FABÍO, FABIÓ, FÁBÍÓ, FÁBIO, etc ...

I hope your help!
Product version: Caché 2018.1
$ZV: 2018.1.2.309.5
Discussion (9)0
Log in or sign up to continue
Fábio Campos · Mar 19, 2021

Good Morning!

Thanks for answering Robert Cemper.

I did the checks as you guided me and everything is as you can see in the images below:

1 - Here we check the installation and the U appears.

2 - Here we can see that ptbw (Portuguese, Brazil, Unicode) is selected.

3 - Here Portuguese2 is selected, it already comes with the installation

Okay, let's get to the facts!

To insert data with accentuation of type Ex .: (áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ) among other types of accents, the database accepts normally and are recorded without problems. See image of an example table that I have here to show

See in the image above, that in the FIRSTNAME field, I have the name Fábio, Fabío and Fabio (two of them with accents and one without an accent)

However, when trying to make a select with some condition to show all results with FABIO (with accents and without accents) the instruction does not return, it shows only the way it was typed in the condition, as you can see in the image below:

What I need is that when making a select using a condition, the database returns all names (Fábio, Fabío and Fabio) regardless of whether they have accents or not.

If so, how could I solve this problem?

Thank you very much and I hope help

0 0
Robert Cemper · Mar 19, 2021

Hi @Fábio Campos 
I see your problem. I don't assume that "Portuguese1" does solve this
So I see 3 workarounds to achieve "shaving" of characters.

#1)  quick & dirty
SELECT * FROM erp.Teste2 WHERE 
$TRANSLATE(firstname,'áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ','aeiouAEIOUcCaeiouAEIOU') like 'fabio'

#2) more  elegant and also for other columns - create a SqlProcedure

ClassMethod shave(par) as %String [SqlProc, SqlName = "shave" ] 
{  quit $TRANSLATE(par,"áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ","'aeiouAEIOUcCaeiouAEIOU") }


applied as 
SELECT * FROM erp.Teste2 WHERE erp.shave(firstname) like 'fabio'

#3) calculated property in your table - to be available also in index
Property firstshaved as %String [Calculated,SqlComputed,
SqlComputeCode = {set {*}=$TRANSLATE(firstname,"áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ","aeiouAEIOUcCaeiouAEIOU")} ];
applied as 
SELECT * FROM erp.Teste2 WHERE firstshave like 'fabio'

0 0
Fábio Campos · Mar 19, 2021

Thank you for the answer!

It worked in parts, let's get to the facts:

1 - I did tests only the first quick test, because I believe that in others the result is similar. Here in the image below, you can see that using an expression, it was case sensitive, as in the case the name fabio was registered with the first capital letter (Fabio) select returned 0

2 -  Here putting the capital F of the word Fabio, the database brings exactly what I expect, all the records recorded in the table.

3 - However, when placing an accented word in the case as an example Fábio, the database does not return any results. As you can see below:

How to make this instruction search for any word (Fabio, fabio, FÁBIO and other possibilities) without being case sensitive and using accents or not?

Thanks again.

0 0
Benjamin De Boe · Mar 23, 2021

Thanks for prodding me on this. It's a perfect opportunity to advertise an upcoming feature that addresses this exact problem.

SQL collations have existed for a long while, but as Robert pointed out, were restricted to playing with upper/lower case, truncation, whitespace and the odd numeric ploy through a limited set of built-in collations. Later this year, we'll release a more generic %COLLATE() option that allows you to combine these properties any way you like, including a translation that removes all accents (both as a SQL collation option and a new $zconvert() option).

In the meantime, you can use the somewhat impractical workarounds described above, or possibly use an iFind index and specify a TRANSFORMATIONSPEC that removes the accents.

2 0