CODEWITHSUNDEEP
oracle-databaseoracle11gcoalesce
Stanley Mungai
Stanley Mungai

Reputation: 4150

ORA-12704: character set mismatch

Hell when I do:

select COALESCE (CORP_ID, 0) from crmuser.accounts;

The CORP_ID records which are Null returns 0 but when I do:

select COALESCE (EMAIL, 'NO EMAIL') from crmuser.accounts

I get an error: 

ORA-12704: character set mismatch

The EMAIL field in NVARCHAR2(30). Is is My Datatype and if so What should I do to return default Values?

Upvotes: 20

Views: 82570

Answers (2)

Cristian Scutaru
Cristian Scutaru

Reputation: 1517

This generic fix works with columns defined as either VARCHAR2 or NVARCHAR2:

select COALESCE (EMAIL, N'' || 'NO EMAIL') from crmuser.accounts

Just add N'' || before your non-Unicode string constant.

Upvotes: 3

DazzaL
DazzaL

Reputation: 21993

you should do

select COALESCE (EMAIL, n'NO EMAIL') from crmuser.accounts

to convert the literal to NVARCHAR.

eg http://sqlfiddle.com/#!4/73929/1 vs http://sqlfiddle.com/#!4/73929/2

Upvotes: 34

Related Questions