0

I have a column that contains NO-BREAK SPACE (\xC2\xA0) instead of SPACE and I need to find that rows.

Copy-pasting works:

SELECT PRODUCT_NAME
FROM TABLE t
WHERE PRODUCT_NAME LIKE '% %'

but using the code points does not:

SELECT PRODUCT_NAME
FROM TABLE t
WHERE PRODUCT_NAME LIKE '%\xC2\xA0%'

How can I find the rows where a colum contains such symbols via \x code points?

Vega
  • 2,661
  • 5
  • 24
  • 49

2 Answers2

0

Try

WHERE regexp_Like(PRODUCT_NAME, UNISTR('\00A0'))

Depending on your database character set you may try CHR(106) instead of UNISTR('\00A0')

xC2 xA0 is not the code point, it is the binary representation of Unicode character U+00A0 (No-Break Space) encoded at UTF-8. The Unicode code point is U+00A0 or 160 (decimal)

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Exasol has no UNISTR() and no REGEXP_LIKE. When I use: SELECT * FROM table WHERE REGEXP_INSTR(PRODUCT_NAME, CHR(106)) -> "where clause must be a boolean expression". CHR(106) is 'j'? – Vega Jan 06 '22 at 13:40
  • @Vega `WHERE regexp_instr(..) > 0` or something would be a boolean expression. Telling data types apart is essential. And understanding error messages. – AmigoJack Jan 06 '22 at 14:44
  • Thanks, adding > 0 works but CHR(160) throws "data exception - argument for chr should be between 0 and 127!". So CHR(160) does not work. – Vega Jan 06 '22 at 14:52
  • The question **was** tagged with "oracle", that's why I provided an Oracle solution. – Wernfried Domscheit Jan 06 '22 at 18:38
0

Exasol does not support \x escape sequences. "\xC2\xA0" is not a code point, but the UTF-8 encoding of the code point U+00A0 (decimal 160). See https://www.compart.com/en/unicode/U+00A0 for more information.

However, you can use UNICODECHR(...):

SELECT PRODUCT_NAME
FROM TABLE t
WHERE PRODUCT_NAME LIKE '%'||unicodechr(160)||'%'

Note that you have to give it the decimal unicode code point. Not the UTF-8 encoding. You can use select to_number('00A0', 'XXXX'); to convert hex to decimal.

If you absolutely need to use \x escape sequences, you can create an UDF. See https://docs.exasol.com/7.1/database_concepts/udf_scripts.htm for more information.

sirain
  • 918
  • 10
  • 19