SQLite – Compare Two Columns and Remove Duplicate Characters

sqlite

Example table:

  ID      CA      CB
  ------  ------  -----
  1       龍      龍 竜 龒
  2       齒      歯 齒 
  3       黽      黽

I would like to modify column CB so that it doesn't contain any of the characters in CA.
Like this:

  ID      CA      CB
  ------  ------  -----
  1       龍       竜 龒
  2       齒      歯 
  3       黽

(SQLite)

Best Answer

Assuming that you mean to remove the character of CA from CB in the same row and that there's always just one character in CA, you can do it like this:

UPDATE your_table
SET CB = REPLACE(CB, CA, '');

Related Solutions

SQLite – Matching Separate Unicode Characters as One

Unicode is a complex beast and database engines tend to be at best superficially aware of it. They certainly don't tend to know about sequence equivalences.

I would suggest making sure that you store data in a consistent normalised form, and normalise search parameters in the same way before searching. I'm not really a c# person, but https://msdn.microsoft.com/en-us/library/ebza6ck1(v=vs.110).aspx looks like a decent place to start for how to go about that.

Obviously you will have existing data that is not normalised, so if you go with this route you'll need to write something to scan the data and replace string values with normalised versions as needed.

SQLite – How to Split a Column into New Columns

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

Best Answer

Related Solutions

SQLite – Matching Separate Unicode Characters as One

SQLite – How to Split a Column into New Columns

Related Question