CODEWITHSUNDEEP
sqloracle-database
Tim
Tim

Reputation: 966

How to select unicode whitespace characters from oracle?

I'm trying to find clobs in an oracle database that contain specific unicode whitespace characters, but I'm not sure how to the select statement can be written.

Trying the following query, but not sure if this is the correct way to go about it:

select * from mytable where my_clob like '%'|| n'0xEF' || '%';

Upvotes: 2

Views: 5036

Answers (2)

eaolson
eaolson

Reputation: 15094

I'm not sure what character you're wanting to search for, but I think it's the UNISTR command you're looking for. Also, LIKE will do an implicit conversion to VARCHAR2 (I think), so you're only searching in the first 4000 characters. Try this to search for the non-breaking space:

SELECT * 
  FROM mytable
 WHERE dbms_lob.instr( my_clob, UNISTR( '\00A0' )) > 0;

https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions224.htm#SQLRF06154

Upvotes: 5

Joachim Isaksson
Joachim Isaksson

Reputation: 181037

If you know the character code of the character you're looking for, you can use CHR() to build an expression to look for the specific code;

SELECT * 
FROM mytable 
WHERE my_clob LIKE '%' || CHR(15052183) || '%';

An SQLfiddle to test with.

Upvotes: 3

Related Questions