This content has been marked as final. Show 5 replies
somthing like this...
WITH t AS (SELECT '1,2,3' "STR" FROM dual) , t1 AS (SELECT SUBSTR(str,1,1) "STR" FROM t UNION ALL SELECT SUBSTR(str,3,1) "STR" FROM t UNION ALL SELECT SUBSTR(str,5,1) "STR" FROM t ) SELECT str ,DECODE(str,1,'India',2,'US',3,'other','defualt') "CNTRY" FROM t1; Output STR CNTRY 1 India 2 US 3 Other
WITH data1(code) AS ( SELECT '1,2,3' FROM dual) SELECT CASE (regexp_substr(code,'[^,][\d]*',1,level)) WHEN '1' THEN 'India' WHEN '2' THEN 'Us' WHEN '3' THEN 'Japan' ELSE 'Others' END states FROM data1 CONNECT BY level <= regexp_count(code,',') + 1 / O/P ------ STATES ------ India Us Japan
GSKumar wrote:Use DECODE
I need a help..
I want to populate the data from varchar2 column, if it has the value 1,2,3 stored in it.
Then I need to pick the values between the commas and show some string for each value available with commas.
For Example: for '1' I should show India.
For '2' I should show US.. etc.
Please post some input data and help us to help you.
DECODE(<data>,1,'India',2,'US',3,'Rest of the World')
According to me, your algorithm should be as follows:
1) Create table dfntn which defines the relation between id and nm
2) Develop generic code which splits the data of any given values..
id nm ----------- 1 India 2 US 3 Other
e.g. 1,2,3 ...
Now join this id with the dfntn. This way u will be maintaining more robust code.. i.e. even though your id assignment changes, u can control that in the table level itself.
id -- 1 2 3 . . .
It was really helpful.