1

Is there a way to be able to use at least _ in the left side so the following statement returns 1:

SELECT 1 FROM DUAL WHERE
'te_ephone' like 'tele_ho%'

I want oracle to parse the left side as it parses the right one, to make _ match 'any' char.
Is this possible or is there any workaround to make this work?

To give some context, the final objective is that things like remoñoson matchs with remonos%.

Left hand side is a column where I am replacing some characters by _ whilst the start with query with the same replacement.

Mario Corchero
  • 5,257
  • 5
  • 33
  • 59

1 Answers1

1

Based on your context what you are expecting can be achieved using Linguistic Sort which gives detailed information about searching Linguistic strings and sorting

Example1(case-insensitive or accent-insensitive comparisons):-

SELECT word FROM test1
WHERE NLS_UPPER(word, 'NLS_SORT = XGERMAN') = 'GROSSE';

WORD
------------
GROSSE
Große
große

Example 2 using Regular expression with the Base Letter Operator [==]:-

Oracle SQL syntax:

SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'r[[=e=]]sum[[=e=]]');

Expression: r[[=e=]]sum[[=e=]]
Matches:

resume

résumé

résume

resumé

psaraj12
  • 4,772
  • 2
  • 21
  • 30
  • Thanks a lot, the problem is that the column contains special characters from all lating languages so it will have accents, ß, ñ, etc... is it possible to use "Base Letter Operator" for a whole word? – Mario Corchero Oct 15 '13 at 08:39
  • Unfortunately you cannot use Base Letter Operator for the whole word ,Can you provide more information regarding your requirement ??? – psaraj12 Oct 15 '13 at 10:42
  • I would like a column to match removing all non ascii chars. What I was doing so far is to convert to ASCII and substitute the ?(for non ascii chars) with _, but I would need to do the same in the column if I want the expected behavior. Problem is that the column stores surnames from people across europe – Mario Corchero Oct 15 '13 at 13:28
  • 1
    For european languages you can use NLS_UPPER on both sides with NLS_SORT=XWEST_EUROPEAN select nls_upper('Groñße','NLS_SORT=XWEST_EUROPEAN') from dual; – psaraj12 Oct 15 '13 at 16:16
  • NLS_UPPER('GROÑßE','NLS_SORT=XWEST_EUROPEAN') --------------------------------------------- GROÑSSE it is not removing the ñ. weird... – Mario Corchero Oct 16 '13 at 08:41
  • Ok i faced the same problem.My understanding from the question and your explanation was the replace in the column is not same as the replace in the variable since some of this accent characters have two letters to represent (for example ß(represented as SS) – psaraj12 Oct 16 '13 at 09:28
  • where NLS_UPPER('GROÑßE','NLS_SORT=XWEST_EUROPEAN')=UPPER('gro___e'); can be used – psaraj12 Oct 18 '13 at 02:42