2

I have a table like this:

CREATE TABLE tab1 ( 
  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, 
  cod TEXT,
  type TEXT,
  qta INTEGER);

INSERT INTO tab1 (cod, type, qta) 
  VALUES ('aaa','aaa,bbb,ccc', 3),
          ('aaa','ddd', 1),
          ('aaa','eee,fff', 4),
          ('aaa','ggg,hhh', 2),
          ('aaa','out', 7),
          ('aaa','out', 7);

I would like to know how many words there are in each cell of column 'type'.

The best for me is to have only the rows that the number of words are the same of 'qta'.

So I would like to have only the rows with id 1, 2, 4

Original link to code: here

GMB
  • 216,147
  • 25
  • 84
  • 135
Francesco G.
  • 679
  • 3
  • 13
  • 32

1 Answers1

3

Consider:

select *
from tab1
where char_length(type) - char_length(replace(type, ',', '')) + 1 = qta

Expression char_length(type) - char_length(replace(type, ',', '')) gives you the number of commas in the string. Adding 1 to that gives you the number of words in the string.

Demo on DB Fiddle:

id | cod | type        | qta
-: | :-- | :---------- | --:
 1 | aaa | aaa,bbb,ccc |   3
 2 | aaa | ddd         |   1
 4 | aaa | ggg,hhh     |   2
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks so much... and if I need to update the string like this aaa_1,bbb_1,ccc_1 – Francesco G. Nov 13 '19 at 11:45
  • *"and if I need to update the string like this aaa_1,bbb_1,ccc_1 "* @FrancescoG. that is a other non related question to your question above more or less requires you writting a complete new question ... – Raymond Nijland Nov 13 '19 at 11:46