Compare strings by their written representation - is it possible?

Question

For example I have a column that contains string (in English - "A", "B" ) "AB1234" and I'd like to compare it to the string "AB1234" (in Russian "A", "B" ), for Example. Is there any built-in function to achieve this?

The best way I found is to use Translate func where i enumerate all needed symbols.

Answer 1

You are looking for a function LOOKS LIKE.

Unfortunately there is no such function in SQL.

Instead, you can create a function-based index that casts every string to a common denominator using TRANSLATE, and search for the string:

CREATE INDEX ix_mytable_transliterate ON (TRANSLATE(UPPER(str), 'АВЕКМНОРСТУХ', 'AВEKMHOPCTYX'))

SELECT  *
FROM    mytable
WHERE   TRANSLATE(UPPER(str), 'АВЕКМНОРСТУХ', 'AВEKMHOPCTYX') = TRANSLATE(UPPER('весна на танке'), 'АВЕКМНОРСТУХ', 'AВEKMHOPCTYX')