ghalib ghalib - 4 months ago 7
SQL Question

SQL select to get specific data depending on column length

I have column

COACODE
storing data like this

ID COACODE
----------------
1 102000

2 1310104


We can see here first record has 6 digits and second record has 7 digits
now i want split the
COACODE
to 3 parts

if
COACODE
= 6 digits length
execute this select ;

select SUBSTR(COACODE, 1, 3) as ENTITY_CODE,SUBSTR(COACODE, 4, 2)as BRANCH_CODE ,SUBSTR(COACODE, 6, 1)as DG_CODE,C.ARABDESC
FROM COACODES c
where CODETYPE=14;


if
COACODE
= 7 digits length
execute this select ;

select SUBSTR(COACODE, 1, 3) as ENTITY_CODE,SUBSTR(COACODE, 4, 2)as BRANCH_CODE ,SUBSTR(COACODE, 6, 2)as DG_CODE,C.ARABDESC
FROM COACODES c
where CODETYPE=14;


So, how i can solve this in sql ????

Answer

Edit: I just check in oracle. Just using SUBSTR(COACODE, 6, 2) will also give you desired result. As for first string, there is only 6th character, it will return 0. For second one, it will return 04, which is what you want.

So depending on your DB, you can try this.

But to answer your question, use case.

select SUBSTR(COACODE, 1, 3) as ENTITY_CODE,
SUBSTR(COACODE, 4, 2)as BRANCH_CODE ,

case when length(COACODE) = 6 
then SUBSTR(COACODE, 6, 1) 
else 
SUBSTR(COACODE, 6, 2)
end as  DG_CODE

FROM COACODES c
where CODETYPE=14;

You can also use just substr and use case inside it like

substr(COACODE,6,case when length(COACODE)=6 then 1 else 2 end)