Posts in category “Database”

Alternative to Oracle Regular expression for word boundaries

You might already know that Oracle regular expression doesn't support \b. However, we need it. So the answer is

(^|\W)yourstring(\W|$)

Reference

ORA-12704: character set mismatch in case statement

I met this issue when I SELECT CASE WHEN d.COUNTRY != 'AU' THEN 'Australia' ELSE countries.NAME END

BTW, I asked ChatGPT for help about this issue but unfortunately I didn't get a good answer.
One of the correct answers was

SELECT CASE WHEN d.COUNTRY != 'AU' THEN 'Australia' ELSE TO_CHAR(countries.NAME) END ...

I found the above answer myself and I told ChatGPT this answer. It replied

Great to hear that you were able to solve the issue! Yes, you can use the TO_CHAR function to convert the character set of the countries.NAME column to match the character set of your database. This is a common solution for character set mismatch errors. Using TO_CHAR function, you can explicitly specify the character set you want to convert to, like this:

TO_CHAR(countries.NAME, 'NLS_CHARACTERSET')

where 'NLS_CHARACTERSET' is the character set of your database. By default, Oracle uses the database's character set for character data that has no specified character set, so if you don't specify a character set for TO_CHAR, it will use the database's character set.

another answer is to use the CAST function in PL/SQL, here's an example from stackoverflow.com