Hello everyone
I have to use an Ingres Database to store my data which is in several
languages (like french, german, and so on). In my web site, the user can
use the function "search".
The problem are the special characters like éÃ*èâ in french, or
öäü in german. The user doesn't enter these characters, but it should
be found anyway.

For example:
In the database is the word "château".
The user types "chateau" (whithout â)
The program should find the "château", even if "chateau" was typed.

So all accents in the database should be replaced by something more
useful (like "_")

Has someone an idea how to do that?

Ingres database version : 9.2.1

Thanks a lot,
Kakmael


--
Kakmael

Hi Kakmael,

That’s a little tricky but here is my first attempt, it could use some polish.

I'm using a table procedure to do the conversion of all the damn foreign characters back into their English counterparts...

create procedure iso88591(
input varchar(200) not null not default
)
result row iso(iso88591 varchar(200) not null not default)
as
declare
bytes integer2 not null not default;
i integer2 not null not default;
ch char(1) not null not default;
output varchar(200) not null not default;
begin
bytes = length(input);
i = 1;
output = '';
while (i <= bytes) do
ch = charextract(input, i);
i = i + 1;
if hex(ch) in ('C0', 'C1', 'C2', 'C3', 'C4', 'C5', 'C6') then
ch = 'A';
elseif hex(ch) in ('E0', 'E1', 'E2', 'E3', 'E4', 'E5', 'E6') then
ch = 'a';
elseif hex(ch) in ('C8', 'C9', 'CA', 'CB') then
ch = 'e';
elseif hex(ch) in ('E8', 'E9', 'EA', 'EB') then
ch = 'e';
elseif hex(ch) in ('D2', 'D3', 'D4', 'D5', 'D6') then
ch = 'O';
elseif hex(ch) in ('F2', 'F3', 'F4', 'F5', 'F6') then
ch = 'o';
endif;
output = output + ch;
endwhile;
return row (utput);
end;

You can now write code like:
select 'asdfg', i.iso88591 from iso88591(input = 'asdfg') i
where i.iso88591 like 'as%'

Although personally speaking I think you'd be well advised to create a separate column (called search_column) in your table for each column you intend to search on. Then update that column with the iso88591 translated characters and use this column for the searching. That would avoid a lot of overhead in subsequent searches. The guts of the above procedure could be used in an insert, update fired procedure to ensure the correct data is stored in the search_column on each insert or update to the table.

Martin Bowes

-----Original Message-----
From: Ingres Forums [mailto:info-ingres (AT) kettleriverconsulting (DOT) com]
Sent: 12 October 2011 08:03
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Select, escaping special chars


Hello everyone
I have to use an Ingres Database to store my data which is in several languages (like french, german, and so on). In my web site, the user can use the function "search".
The problem are the special characters like éÃ*èâ in french, or öäü in german. The user doesn't enter these characters, but it should be found anyway.

For example:
In the database is the word "château".
The user types "chateau" (whithout â)
The program should find the "château", even if "chateau" was typed.

So all accents in the database should be replaced by something more useful (like "_")

Has someone an idea how to do that?

Ingres database version : 9.2.1

Thanks a lot,
Kakmael


--
Kakmael

Hello!
Outch okay.. a procedure like that for each searching request.. that
may ask a lot of performance to the server.
I hoped there'll be an "official" way to do this. Maybe I should do
that string treatment on the client side, transforming each A,E,I,O,U
into a "_" . But that could give me weird searches sometimes..
A new row / table for searches would be another solution, I've to dig
deeper!

Thank you for the ideas; I'll look what I can do with that

Kakmael


--
Kakmael

Hi Kakmael,

As I mentioned in my OP I suspect you'd be better off defining a search_field column counterpart for each field you intend to search on and having a Rules fired procedure to maintain those columns using the gist of the table procedure I mentioned earlier. That way each searching request can be written as a standard
where search_field like '...'. It would be a lot easier on the server.

Marty

-----Original Message-----
From: Ingres Forums [mailto:info-ingres (AT) kettleriverconsulting (DOT) com]
Sent: 13 October 2011 12:22
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] Select, escaping special chars


Hello!
Outch okay.. a procedure like that for each searching request.. that
may ask a lot of performance to the server.
I hoped there'll be an "official" way to do this. Maybe I should do
that string treatment on the client side, transforming each A,E,I,O,U
into a "_" . But that could give me weird searches sometimes..
A new row / table for searches would be another solution, I've to dig
deeper!

Thank you for the ideas; I'll look what I can do with that

Kakmael


--
Kakmael


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

One thing you can do - which may not be appropriate depending on your needs - is create a custom collation sequence. The collation sequence determines what "weight" individual characters have and therefore the order in which they sort. It's possible to define similar characters to have the same weight.

You do this by creating a text file that describes the collation sequence (.dsc) and compiling it with aducompile. You then set the collation sequence when you create the database using the -l flag

E.g.

a:â
a:ã
a:ä

Creating DBMS System Catalogs . . .
Modifying DBMS System Catalogs . . .
Creating Standard Catalog Interface . . .
Creating Front-end System Catalogs . . .

Creation of database 'collatedb' completed successfully.

Ingres Linux Version II 10.1.0 (int.lnx/00)NPTL login
Fri Oct 14 10:42:03 2011
continue
* * * /* SQL Startup File */
CREATE TABLE test (col1 INTEGER NOT NULL,
col2 VARCHAR(20) NOT NULL)
Executing . . .

continue
* * * * * * * * INSERT INTO test VALUES (1, 'Cháteau');
INSERT INTO test VALUES (2, 'Château');
INSERT INTO test VALUES (3, 'Chãteau');
INSERT INTO test VALUES (4, 'Chäteau');
INSERT INTO test VALUES (5, 'Chateau');
INSERT INTO test VALUES (6, 'xyz');
INSERT INTO test VALUES (7, 'abc');
Executing . . .

(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
continue
* * * SELECT * FROM test
WHERE col2 = 'Chateau'
Executing . . .


+-------------+--------------------+
(5 rows)
continue
* * * SELECT * FROM test
WHERE col2 LIKE '%a%'
Executing . . .


+-------------+--------------------+
(6 rows)
continue
* * * SELECT * FROM test
ORDER BY col2
Executing . . .


+-------------+--------------------+
(7 rows)
continue
* * SELECT DISTINCT col2 FROM test
Executing . . .


+--------------------+
(3 rows)
continue
*
Your SQL statement(s) have been committed.

Ingres Version II 10.1.0 (int.lnx/00)NPTL logout
Fri Oct 14 10:42:03 2011

The downsides are that

a) as you can see above you lose the ability to distinguish between those values (see the SELECT DISTINCT query)

b) since it needs setting when the database is created, an existing database would need to be unloaded and then reloaded.

As I say these downsides may outweigh the benefit.

HTH
Paul

If it is a case and diacritical insensitive compare you need then you can use collation_weight:

For instance:
SELECT LEFT(COLLATION_WEIGHT(NVARCHAR('Âőñ')),6)

will give the same results as:

SELECT LEFT(COLLATION_WEIGHT(NVARCHAR('aon')),6)

So your selection could be:

SELECT ... WHERE LEFT(COLLATION_WEIGHT(NVARCHAR(c)),2*LENGTH(c))=LE FT(COLLATION_WEIGHT(NVARCHAR('key')),2*LENGTH('key '))

You must only use the 2*length bytes from collation_weight and it must use the nvarchar cast and then you will be comparing the base characters.

Regards,
Ian


-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Paul Mason
Sent: 14 October 2011 11:55
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Select, escaping special chars

One thing you can do - which may not be appropriate depending on your needs - is create a custom collation sequence. The collation sequence determines what "weight" individual characters have and therefore the order in which they sort. It's possible to define similar characters to have the same weight.

You do this by creating a text file that describes the collation sequence (.dsc) and compiling it with aducompile. You then set the collation sequence when you create the database using the -l flag

E.g.

a:â
a:ã
a:ä

Creating DBMS System Catalogs . . .
Modifying DBMS System Catalogs . . .
Creating Standard Catalog Interface . . .
Creating Front-end System Catalogs . . .

Creation of database 'collatedb' completed successfully.

Ingres Linux Version II 10.1.0 (int.lnx/00)NPTL login
Fri Oct 14 10:42:03 2011
continue
* * * /* SQL Startup File */
CREATE TABLE test (col1 INTEGER NOT NULL,
col2 VARCHAR(20) NOT NULL)
Executing . . .

continue
* * * * * * * * INSERT INTO test VALUES (1, 'Cháteau');
INSERT INTO test VALUES (2, 'Château');
INSERT INTO test VALUES (3, 'Chãteau');
INSERT INTO test VALUES (4, 'Chäteau');
INSERT INTO test VALUES (5, 'Chateau');
INSERT INTO test VALUES (6, 'xyz');
INSERT INTO test VALUES (7, 'abc');
Executing . . .

(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
continue
* * * SELECT * FROM test
WHERE col2 = 'Chateau'
Executing . . .


+-------------+--------------------+
(5 rows)
continue
* * * SELECT * FROM test
WHERE col2 LIKE '%a%'
Executing . . .


+-------------+--------------------+
(6 rows)
continue
* * * SELECT * FROM test
ORDER BY col2
Executing . . .


+-------------+--------------------+
(7 rows)
continue
* * SELECT DISTINCT col2 FROM test
Executing . . .


+--------------------+
(3 rows)
continue
*
Your SQL statement(s) have been committed.

Ingres Version II 10.1.0 (int.lnx/00)NPTL logout
Fri Oct 14 10:42:03 2011

The downsides are that

a) as you can see above you lose the ability to distinguish between those values (see the SELECT DISTINCT query)

b) since it needs setting when the database is created, an existing database would need to be unloaded and then reloaded.

As I say these downsides may outweigh the benefit.

HTH
Paul



_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Hi Paul,

Oh that's sweet! But I take it that this can't be done after the database is created with another collation.

And on anoter point...how the hell did you enter the á etc at a standard keyboard?

Marty
-----Original Message-----
From: Paul Mason [mailto:Paul.Mason (AT) actian (DOT) com]
Sent: 14 October 2011 11:55
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Select, escaping special chars

One thing you can do - which may not be appropriate depending on your needs - is create a custom collation sequence. The collation sequence determines what "weight" individual characters have and therefore the order in which they sort. It's possible to define similar characters to have the same weight.

You do this by creating a text file that describes the collation sequence (.dsc) and compiling it with aducompile. You then set the collation sequence when you create the database using the -l flag

E.g.

a:â
a:ã
a:ä

Creating DBMS System Catalogs . . .
Modifying DBMS System Catalogs . . .
Creating Standard Catalog Interface . . .
Creating Front-end System Catalogs . . .

Creation of database 'collatedb' completed successfully.

Ingres Linux Version II 10.1.0 (int.lnx/00)NPTL login
Fri Oct 14 10:42:03 2011
continue
* * * /* SQL Startup File */
CREATE TABLE test (col1 INTEGER NOT NULL,
col2 VARCHAR(20) NOT NULL)
Executing . . .

continue
* * * * * * * * INSERT INTO test VALUES (1, 'Cháteau');
INSERT INTO test VALUES (2, 'Château');
INSERT INTO test VALUES (3, 'Chãteau');
INSERT INTO test VALUES (4, 'Chäteau');
INSERT INTO test VALUES (5, 'Chateau');
INSERT INTO test VALUES (6, 'xyz');
INSERT INTO test VALUES (7, 'abc');
Executing . . .

(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
continue
* * * SELECT * FROM test
WHERE col2 = 'Chateau'
Executing . . .


+-------------+--------------------+
(5 rows)
continue
* * * SELECT * FROM test
WHERE col2 LIKE '%a%'
Executing . . .


+-------------+--------------------+
(6 rows)
continue
* * * SELECT * FROM test
ORDER BY col2
Executing . . .


+-------------+--------------------+
(7 rows)
continue
* * SELECT DISTINCT col2 FROM test
Executing . . .


+--------------------+
(3 rows)
continue
*
Your SQL statement(s) have been committed.

Ingres Version II 10.1.0 (int.lnx/00)NPTL logout
Fri Oct 14 10:42:03 2011

The downsides are that

a) as you can see above you lose the ability to distinguish between those values (see the SELECT DISTINCT query)

b) since it needs setting when the database is created, an existing database would need to be unloaded and then reloaded.

As I say these downsides may outweigh the benefit.

HTH
Paul



_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Hi Marty,
I use vi to build the diacriticals as my keyboard is not very good with languages, (even English spelling it finds taxing).
In vi use control-k followed by a' or a` or n~ etc. then cut and paste ...
Regards,
Ian
-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 14 October 2011 14:38
To: Ingres and related product discussion forum
Cc: Paul Mason
Subject: Re: [Info-Ingres] Select, escaping special chars

Hi Paul,

Oh that's sweet! But I take it that this can't be done after the database is created with another collation.

And on anoter point...how the hell did you enter the á etc at a standard keyboard?

Marty
-----Original Message-----
From: Paul Mason [mailto:Paul.Mason (AT) actian (DOT) com]
Sent: 14 October 2011 11:55
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Select, escaping special chars

One thing you can do - which may not be appropriate depending on your needs - is create a custom collation sequence. The collation sequence determines what "weight" individual characters have and therefore the order in which they sort. It's possible to define similar characters to have the same weight.

You do this by creating a text file that describes the collation sequence (.dsc) and compiling it with aducompile. You then set the collation sequence when you create the database using the -l flag

E.g.

a:â
a:ã
a:ä

Creating DBMS System Catalogs . . .
Modifying DBMS System Catalogs . . .
Creating Standard Catalog Interface . . .
Creating Front-end System Catalogs . . .

Creation of database 'collatedb' completed successfully.

Ingres Linux Version II 10.1.0 (int.lnx/00)NPTL login
Fri Oct 14 10:42:03 2011
continue
* * * /* SQL Startup File */
CREATE TABLE test (col1 INTEGER NOT NULL,
col2 VARCHAR(20) NOT NULL)
Executing . . .

continue
* * * * * * * * INSERT INTO test VALUES (1, 'Cháteau');
INSERT INTO test VALUES (2, 'Château');
INSERT INTO test VALUES (3, 'Chãteau');
INSERT INTO test VALUES (4, 'Chäteau');
INSERT INTO test VALUES (5, 'Chateau');
INSERT INTO test VALUES (6, 'xyz');
INSERT INTO test VALUES (7, 'abc');
Executing . . .

(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
(1 row)
continue
* * * SELECT * FROM test
WHERE col2 = 'Chateau'
Executing . . .


+-------------+--------------------+
(5 rows)
continue
* * * SELECT * FROM test
WHERE col2 LIKE '%a%'
Executing . . .


+-------------+--------------------+
(6 rows)
continue
* * * SELECT * FROM test
ORDER BY col2
Executing . . .


+-------------+--------------------+
(7 rows)
continue
* * SELECT DISTINCT col2 FROM test
Executing . . .


+--------------------+
(3 rows)
continue
*
Your SQL statement(s) have been committed.

Ingres Version II 10.1.0 (int.lnx/00)NPTL logout
Fri Oct 14 10:42:03 2011

The downsides are that

a) as you can see above you lose the ability to distinguish between those values (see the SELECT DISTINCT query)

b) since it needs setting when the database is created, an existing database would need to be unloaded and then reloaded.

As I say these downsides may outweigh the benefit.

HTH
Paul



_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

The two options I see:
- using a collation file for the database
- store the soundex version of the strings in the database and to do a
soundex on the search string before searching the database


--
fba

The two options I see:
- using a collation file for the database
- store the soundex version of the strings in the database and to do a
soundex on the search string before searching the database