1

I'm using oracle 11g database, does oracle database have a mechanism to replace specific character with other characters over all tables when storing data. For example i want to go to specific configuration place in oracle and specify these characters , to be replaced with these ک, ی that are Arabic characters.

I'm beginner in oracle database.

  • If this is equivalent to turning letters are like à, ä, A, to a, there is collation (newest versions of Oracle). It won't change the characters, but it will allow you to search. – Michael Kutz Jun 30 '18 at 18:23
  • Yes, it is. does oracle 11g support collation? – Rasool Ghafari Jul 1 '18 at 4:59
  • There is no automatic mechanism that will change characters when storing them into the database (whether by insert or update). What happens automatically is the re-encoding of characters from the character set used for the input, to that used in the database. For example if your input is in ISO-8859-6 and your database uses UTF8, then the input characters will be internally converted to the UTF8 encoding. But the characters themselves (the glyphs) don't change. Only their internal binary encoding. – Albert Godfrind Jul 2 '18 at 8:07
  • When the database character set does not include the input characters, then they will be encoded as "?". That would happen when inserting strings in ISO-8859-6 into a database that uses ISO-8859-1 (iso latin 1) which does not understand Arabic characters. In some cases, like going from ISO-8859-1 to US7ASCII, accented latin letters will be folded (é, è,ê all become e). – Albert Godfrind Jul 2 '18 at 8:09
  • Arabic has this extra complexity that the same letter can have multiple glyphs depending on the writing style. So unicode characters U+0643 ك, U+06A9 ک, (and U+06AA ڪ ) are all forms of the Arabic letter kaf, written in different styles. Meaning that the TRANSLATE() function can be used to change from one writing style to the other. The solution would then be to use a trigger to automatically do this transformation. – Albert Godfrind Jul 2 '18 at 8:24
1

The database does not offer such an option that you asked for.

It is the responsibility of the client/application to insert correct data into the database.

If you want to change existing data, you can use the TRASNLATE function to replace each occurence of specific characters.

update table1 
set column1 = translate(column1, 'AB', 'XY')
where column1 != translate(column1, 'AB', 'XY');
| improve this answer | |
  • You could mention that an INSERT trigger would do the job – Philᵀᴹ Jun 30 '18 at 18:20
  • Is it can be done with setting collation? and does oracle 11g supports collation? – Rasool Ghafari Jul 1 '18 at 5:00
0

There is no automatic mechanism that will change characters when storing them into the database (whether by insert or update). What happens automatically is the re-encoding of characters from the character set used for the input, to that used in the database. For example if your input is in ISO-8859-6 and your database uses UTF8, then the input characters will be internally converted to the UTF8 encoding. But the characters themselves (the glyphs) don't change. Only their internal binary encoding.

When the database character set does not include the input characters, then they will be encoded as "?". That would happen when inserting strings in ISO-8859-6 into a database that uses ISO-8859-1 (iso latin 1) which does not understand Arabic characters. In some cases, like going from ISO-8859-1 to US7ASCII, accented latin letters will be folded (é, è, ê all become e).

Arabic has this extra complexity that the same letter can have multiple glyphs depending on the writing style. So unicode characters U+0643 ك, U+06A9 ک, (and U+06AA ڪ ) are all forms of the Arabic letter kaf, written in different styles. Meaning that the TRANSLATE() function can be used to change from one writing style to the other. The solution would then be to use a trigger to automatically do this transformation. Here is an example:

Create a test table:

SQL> create table t1 (i number, s varchar2(30));
Table created.

Create the trigger. That trigger actions whenever you insert a new row into that table, or whenever column s is updated. The :new pseudo-variable refers to the new values in the row being updated (or inserted).

SQL> create or replace trigger t1_t 
before insert or update of s on t1
for each row
begin
  :new.s := translate(:new.s,'ك','ک');
end;
/
Trigger created.

Insert a couple of strings (single character for two styles of kaf):

SQL> insert into t1 values (1,'ك');
1 row created.

SQL> insert into t1 values (2,'ک');
1 row created.

Check the results. Both have the same style now:

SQL> select * from t1;

  I S
--- ----------
  1 ک
  2 ک

2 rows selected.

You can also see the unicode code points:

SQL> select i, asciistr(s) u from t1;

  I U
--- ----------
  1 \06A9
  2 \06A9

2 rows selected.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.