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|$)
You might already know that Oracle regular expression doesn't support \b
. However, we need it. So the answer is
(^|\W)yourstring(\W|$)
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