CODEWITHSUNDEEP
sqloracle-database
Dewitt Harrison
Dewitt Harrison

Reputation: 51

'Character Set Missmatch' error in a CASE statement

I'm trying to get the else part of Oracle case statement to return a set string value; Field1 is a NVARCHAR2(255 CHAR)

select case when substr(field1, 10, 1) = 'x' 
         then substr(field1, 9, 3) 
         else '---'
       end Data
  from table a

Example of data

Row       field1
1         Current 1x1 Cost
2         Current Cost 
3         Current 1x1 Efficiency
4         Current Efficiency
5         Current 1x1 CostB
6         Current CostB

What I want to return

Row       Data
1         1x1
2         ---
3         1x1
4         ---
5         1x1
6         ---

Upvotes: 1

Views: 64

Answers (1)

Robert
Robert

Reputation: 25753

Try to cast --- to NVARCHAR2 as below

select case when substr(field1, 10, 1) = 'x' 
         then substr(field1, 9, 3) 
         else cast('---' as NVARCHAR2(255))
       end Data
  from tab a

Now evry value returns the same type of data

Sql Fiddle Demo

Upvotes: 2

Related Questions