SQLite: UPDATE column Y with a sub-string from column X in a string containing multi-separators

Question

I have a table with two columns PARTNAME and POSTFIX. PARTNAME - the name of a construction part consists of different substrings separated by '-'. Syntax:

aaaa-bbbb-ddddd-ccc-gggg

The length of every substring is dynamic, the amount of '-' in the partnames is dynamic too. I want to put the substring (ggggggg) after the LAST '-' into the column POSTFIX which is empty right now. I look for a GENERIC answer, but I ask myself how would I ...

1) ... put just the substring "gggggg" into POSTFIX column
2) ... put just the substring "dddd" (3rd part of the string) into POSTFIX column
3) ... put the substring "cccc-gggg" into POSTFIX column

Here is my code for now, but it is static and does not work:

update parts
set postfix = case 
    when instr(partname, '-') > 0 then substr(partname, length(partname) - 3, 4)
    else NULL
end

It's much, much better to store each of those substrings in its own row in a table. — Shawn, Jan 13 '19 at 14:02
@Shawn, I agree with you 100%. I just got the data in this way and now I want to normalize it somehow. This is exactly why I ask this question. I am looking for "tools" to separate the data. — A.T., Jan 13 '19 at 19:03
Would help a lot if you created a SQLFiddle and seeded it with a few examples of the different combinations you are seeing. — Kenneth, Jan 14 '19 at 23:32
Wow, this totally answers my question. Just one short clarification. I did not express myself very clearly. The are no double "--" dashes. Just the amount of dash occurences in the strings varies. Like: aaaa-bbbb-ddddd-ccc-gggg aaaa-bbbb-ddddd aaaa-bbbb aaaa-bbbb-ddddd-ccc — A.T., Jan 15 '19 at 15:31

score 1 · Accepted Answer · answered Jan 15 '19 at 00:47

It's messy, but based on the answer to this question How to get the last index of a substring in SQLite?

SQLFiddle with live example here This may not be as efficient as it can be. It's ugly. That's for sure!

As for a variable number of dashes, well, if it's insignificant how many dashes there are, you could execute a query first to replace all double dashes '--' with single dashes '-'. Run that multiple times if necessary if there are occurrences of more than two dashes.

SQLite Solution

CREATE TABLE `parts` (
  `partname`    TEXT,
  `postfix1`    TEXT,
  `postfix2`    TEXT,
  `postfix3`    TEXT
);


INSERT INTO `parts`
  (`partname`)
VALUES
  ('aaaa-bbbb-ddddd-ccc-ggggggg'),
  ('aaaa-bbbbb-dddd-cccc-gggggg'),
  ('aaaa-bbbbbb-ddd-ccccc-ggggg'),
  ('aaaa-bbbbbbb-dd-cccccc-gggg')
;


UPDATE `parts`
SET `postfix1` = CASE 
    WHEN instr(`partname`, '-') > 0 THEN
      replace(`partname`, rtrim(`partname`, replace(`partname`, '-', '')), '')
    ELSE NULL
END;

UPDATE `parts`
SET `postfix3` = CASE 
    WHEN instr(`partname`, '-') > 0 THEN
      replace(`partname`, rtrim(
        rtrim( rtrim(`partname`, replace(`partname`, '-', '')), '-'),
      replace(`partname`, '-', '')), '')
    ELSE NULL
END;

UPDATE `parts`
SET `postfix2` = CASE 
    WHEN instr(`partname`, '-') > 0 THEN

    replace(

        replace(`partname`,
            '-' || replace(`partname`, rtrim( rtrim( rtrim(`partname`, replace(`partname`, '-', '')), '-'), replace(`partname`, '-', '')), ''),
        ''),

        rtrim(

          replace(`partname`,
            '-' || replace(`partname`, rtrim( rtrim( rtrim(`partname`, replace(`partname`, '-', '')), '-'), replace(`partname`, '-', '')), ''),
          ''),

            replace(
              replace(`partname`,
                '-' || replace(`partname`, rtrim( rtrim( rtrim(`partname`, replace(`partname`, '-', '')), '-'), replace(`partname`, '-', '')), ''),
              ''),
            '-', '')
        ),
    '')
    ELSE NULL
END;

SELECT * FROM `parts`;

Gives the results

partname                        postfix1    postfix2   postfix3
--------------------------------------------------------------------
"aaaa-bbbb-ddddd-ccc-ggggggg"   "ggggggg"   "ddddd"    "ccc-ggggggg"
"aaaa-bbbbb-dddd-cccc-gggggg"   "gggggg"    "dddd"     "cccc-gggggg"
"aaaa-bbbbbb-ddd-ccccc-ggggg"   "ggggg"     "ddd"      "ccccc-ggggg"
"aaaa-bbbbbbb-dd-cccccc-gggg"   "gggg"      "dd"       "cccccc-gggg"

SQLite: UPDATE column Y with a sub-string from column X in a string containing multi-separators

1 Answers1

SQLite Solution

Gives the results