Search code examples
sqlms-accesssql-query-store

How to split columns on basis of length

I have a table of 10 columns,I want to split one column into two columns if length of value of column is four,otherwise no split,for rest of values it should enter first splitted column.

column  Acolumn Bcolumn C

0000    00      00
1111   
1 1
000     000 
0115    01      15
Solution

  • You want to do something conditionally in MS Access. That usually means IIF() or SWITCH(), because MS Access does not support the standard CASE expression syntax:

    select t.column,
       iif(len(column) = 4, left(column, 2), column) as columnA,
       iif(len(column) = 4, right(column, 2), NULL) as columnB
from t;

    Although I don't recommend it, you can also use union all:

    select t.column, t.column as columnA, NULL as columnB
from t
where len(column) <> 4
union all
select t.column, left(t.column, 2) as columnA, right(t.column, 2) as columnB
from t
where len(column) = 4;