I have alpanumeric,character and interger values in my column,by using case i want to char values as 'C',int values as 'I'.

General
Enthusiast

I have alpanumeric,character and interger values in my column,by using case i want to char values as 'C',int values as 'I'.

 
4 REPLIES
Enthusiast

Re: I have alpanumeric,character and interger values in my column,by using case i want to char values as 'C',int values as 'I'.

Enthusiast

Re: I have alpanumeric,character and interger values in my column,by using case i want to char values as 'C',int values as 'I'.

Which database version you are using? Did you try using regular expressions?
Enthusiast

Re: I have alpanumeric,character and interger values in my column,by using case i want to char values as 'C',int values as 'I'.

Hi,

I am using 13.0,but I tried not working...
I have int values,character and alpa numeric values in my column.
But I want to display int values as 'I' and alpa numeric values as 'AN' and char values as 'C'.By using case statement how can we display?
Senior Apprentice

Re: I have alpanumeric,character and interger values in my column,by using case i want to char values as 'C',int values as 'I'.

Hi Purushotham,

without UDF or RegEx there's no easy way to determine that.

You could create a monster CASE to check each character for it's character class:

CASE WHEN SUBSTRING(col FROM 1 FOR 1) NOT BETWEEN '0' AND '9'
OR SUBSTRING(col FROM 2 FOR 1) NOT BETWEEN '0' AND '9'
OR SUBSTRING(col FROM 3 FOR 1) NOT BETWEEN '0' AND '9'
...
THEN 'C'