0

Example table:

  ID    CB        CB2   CB3   CB4  
  ----  --------  ----  ----  ---- 
  1     亀 龜 龜
  2     竜 龒 

Note: Each character is separated by a space. Note: The number of characters in CB varies.

I would like to move each character in column CB (after the first) to its own, separate, column, so that no more than one character is in each column.

Like this:

  ID    CB    CB2   CB3   CB4    
  ----  ----  ----  ----  ----
  1      亀    龜    龜
  2      竜    龒

(SQLite)

  • Can you post ddl of example table?Maximum length of column CB is always 3?. – Sam Jun 24 '18 at 2:23
  • CB2, CB3, CB4 aren't actual columns in my table. I added them to the first example table, because their absence led to some confusion in an earlier post (someone suggested a recursive CTE). Though, I will manually add columns CB2, CB3, CB4..., to my table, if it isn’t possible to create the columns and add data to them in one execution. The maximum is 5. – Afh Jun 24 '18 at 3:35
  • Ddl? ID is INTEGER; CB* is TEXT. – Afh Jun 24 '18 at 3:39
0

Worked with an example of CB column with maximum character size of 3 sqllite(WeB),just a demonstration how to split characters.

   CREATE TABLE TABLE1 (ID number,CB text(3),CB2 text(1),CB3 text(1),CB4 text(1));

INSERT INTO TABLE1 VALUES(1,'ABC','','','');
INSERT INTO TABLE1 VALUES(2,'DE','','','');

  select id,
CASE  
 WHEN LENGTH(CB) >= 1 THEN SUBSTR(CB,1,1) 
 END CB,

CASE    
 WHEN LENGTH(CB) >= 2 THEN SUBSTR(CB,2,1) 
 END CB2,

CASE    
 WHEN LENGTH(CB) >= 3 THEN SUBSTR(CB,3,1) 
 END CB3

from
table1

Note:Not a solution,you can work out from this idea.

Updating on same table

   --table creation
CREATE TABLE TAB1 (ID integer,CB text(5),CB1 text(1),CB2 text(1),CB3 text(1),CB4 text(1));
 --- Insert sample rows  
 INSERT INTO TAB1 VALUES(1,'ABCDE','','','','');
 INSERT INTO TAB1 VALUES(2,'DE','','','','');
 INSERT INTO TAB1 VALUES(3,'KLM','','','','');

---update rows
  UPDATE tab1
  SET
    cb  = CASE WHEN length(cb) > 1  THEN substr(cb,1,1)  END,
    cb1 = CASE WHEN length(cb) >= 2  THEN substr(cb,2,1) END,
    cb2 = CASE WHEN length(cb) >= 3  THEN substr(cb,3,1) END,
    cb3 = CASE WHEN length(cb) >= 4  THEN substr(cb,4,1) END,
    cb4 = CASE WHEN length(cb) >= 5  THEN substr(cb,5,1) END   
  WHERE id IN ( SELECT id FROM tab1);

If you have two tables just join by id(inner) and update target table by using case statement

  • Could your example be easily modified to update TABLE1? – Afh Jun 24 '18 at 5:04
  • Yes you can do updates with case statements. set CB1= CASE WHEN LENGTH(CB) >= 1 THEN SUBSTR(CB,1,1) else CB END – Sam Jun 24 '18 at 5:40
  • UPDATE TABLE1 set CB= CASE WHEN LENGTH(CB) >= 1 THEN SUBSTR(CB,1,1) else CB END, CB2= CASE WHEN LENGTH(CB) >= 2 THEN SUBSTR(CB,2,1) else CB END CB3= CASE WHEN LENGTH(CB) >= 3 THEN SUBSTR(CB,3,1) else CB END; – Afh Jun 24 '18 at 7:00
  • I’m… not sure I understand. My goal in asking this question was to be able to update my table. If it wouldn’t be too much trouble, could you edit the answer you gave, to demonstrate how to do updates with case statements? – Afh Jun 24 '18 at 7:00
  • Thanks! I don't have the SQL I executed saved, but I think all I had to do was replace 2,3,4,5 with 3,5,7,9. – Afh Jun 28 '18 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.