-3

I have this table:

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL,
  `value` longtext,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

With this data:

id, value
--------------------
'1', '__cccccccc; Myword'
'2', '__ddddddd; Myword'
'3', 'OOO; myword; anotherword'

And this query:

SELECT `mytable`.`value`
     , LENGTH(`mytable`.`value`) 'string length'
     , LENGTH(REPLACE(LOWER(`mytable`.`value`),'; myword','')) 'after replace'
     , LENGTH(`mytable`.`value`) - LENGTH(REPLACE(`mytable`.`value`,'; myword','')) 'after sub'
     , LENGTH(`mytable`.`value`) - LENGTH(REPLACE(`mytable`.`value`,'; myword',''))/8 'after divide'
  FROM mytable 
 WHERE LOWER(`mytable`.`value`) LIKE '%; myword%' 
    OR LOWER(`mytable`.`value`) LIKE '%; myword';

And this result:

# value, string length, after replace, after sub, after divide
'__cccccccc; Myword', '18', '10', '8', '16.7500'
'__ddddddd; Myword', '17', '9', '8', '15.8750'
'OOO; myword; anotherword', '24', '16', '8', '22.0000'

The insert statement:

INSERT INTO `test`.`mytable`
(`id`,
`value`)
VALUES
('1','__cccccccc; Myword'),
('2','__ddddddd; Myword'),
('3','OOO; myword; anotherword');

The problem is the last column result is incorrect. For example, the last column in the first record should be 8/8 which is 1. Why am I getting these results? How to correct them plz with the minimal changes?

Note: I need to convert the strings to lowercase using LOWER because replace is case sensitive and my values are case sensitive (I need to capture any occurrence of the keyword ; myword whether it contains capital or small letters, I do not care.

EDIT:

The query aims to count the number of times the string ; myword appeared. So I count the length of the field, and subtract it from the length of the field after replacing the keyword I want to count with nothing ''. Then divide the result with the number of characters in the keyword string (8 in my case).

None
  • 281
  • 1
  • 6
  • 16

3 Answers3

0

The source of the problem is that REPLACE is case sensitive. So the select statement should be:

SELECT `mytable`.`value`
     , LENGTH(`mytable`.`value`) 'string length'
     , LENGTH(REPLACE(LOWER(`mytable`.`value`),'; myword','')) 'after replace'
     , (LENGTH(`mytable`.`value`) - LENGTH(REPLACE(LOWER(`mytable`.`value`),'; myword',''))) 'after sub'
     , (LENGTH(`mytable`.`value`) - LENGTH(REPLACE(LOWER(`mytable`.`value`),'; myword','')))/8 'after divide'
FROM mytable 
WHERE LOWER(`mytable`.`value`) LIKE '%; myword%' 
#OR LOWER(`mytable`.`value`) LIKE '%; myword';

You can see I added LOWER() inside each REPLACE() because REPLACE() is case sensitive and you search for keyword with small letters, so you must convert your text to small to capture every occurrence of the keyword despite the letter case.

With this query, you get this result:

# value, string length, after replace, after sub, after divide
'__cccccccc; Myword', '18', '10', '8', '1.0000'
'__ddddddd; Myword', '17', '9', '8', '1.0000'
'OOO; myword; anotherword', '24', '16', '8', '1.0000'

so the last column correctly shows the number of occurrence of the keyword in the value field.

None
  • 281
  • 1
  • 6
  • 16
-1

Try this function

delimiter $$
CREATE FUNCTION `count_str`(long_text_column TEXT, string_to_find VARCHAR(32)) RETURNS int(11)
    DETERMINISTIC
BEGIN
    RETURN ROUND((CHAR_LENGTH(long_text_column) - CHAR_LENGTH(REPLACE(long_text_column, string_to_find, ""))) / CHAR_LENGTH(string_to_find));
  END $$
delimiter ;
James
  • 1,819
  • 2
  • 8
  • 21
  • Thanks. But this is total change to my query. I do not want to change it Just solve the issue of division or understand how to fix that issue. I want minimal changes. I can not replace it with a whole new query. – None Jul 16 '19 at 11:05
  • just use the logic there and you need to split as per your query – James Jul 16 '19 at 11:07
-1

So by splitting based on your query, check out this

SELECT `mytable`.`value`
     , LENGTH(`mytable`.`value`) 'string length'
     , LENGTH(REPLACE(LOWER(`mytable`.`value`),'; myword','')) 'after replace'
     , LENGTH(`mytable`.`value`) - LENGTH(REPLACE(`mytable`.`value`,'; myword','')) 'after sub'
     , (LENGTH(`mytable`.`value`) - LENGTH(REPLACE(`mytable`.`value`,'; myword','')))/8 'after divide'
  FROM mytable 
 WHERE LOWER(`mytable`.`value`) LIKE '%; myword%' 
    OR LOWER(`mytable`.`value`) LIKE '%; myword';
James
  • 1,819
  • 2
  • 8
  • 21
  • can you clarify plz? I do not get what you mean by splitting? That you divided the selected fields into lines this never affects the results. However, I noticed you removed the LOWER() which I need it necessarily becasue the table data are sometimes in capital and sometimes in small letters. I need to convert them all to LOWER to avoid this case sensitivity issue. – None Jul 16 '19 at 11:57
  • does the above query works? and by default mysql will not check based on cases. or share create table and data samples – James Jul 16 '19 at 11:59
  • REPLACE is case sensitive function. I checked it now. Due to case sensitive issue, your query does not find the the keyword in the first two records and returns zero. – None Jul 16 '19 at 12:26
  • share create & sample data statements for table, so that can test locally – James Jul 16 '19 at 12:36
  • They are shared in the OP! – None Jul 17 '19 at 12:00
  • the query doesn't match with the data sample. share insert structure and the same query to fetch rather than x.value which is not part of the table – James Jul 17 '19 at 12:02
  • Sorry for the confusion. Unfortunately, someone edited my post to make this confusion. It is edited now. Plz check. – None Jul 17 '19 at 12:11
  • Insert statement in the sense need the insert query of data so that can test locally – James Jul 17 '19 at 12:13
  • You can find it now. I'm not sure if this what you want. – None Jul 17 '19 at 12:20
  • yeah, that's correct.. and your result set is in comma separated if you show in a tabular format it would be much better, with Expected result – James Jul 17 '19 at 12:22
  • I figured out the source of the problem. Will post an answer. Thanks. – None Jul 17 '19 at 12:26