Asked  7 Months ago    Answers:  5   Viewed   32 times

I have a table like this:

TITLE          |   DESCRIPTION
------------------------------------------------
test1          |   value blah blah value
test2          |   value test
test3          |   test test test
test4          |   valuevaluevaluevaluevalue

I am trying to figure out how to return the number of times a string occurs in each of the DESCRIPTION's.

So, if I want to count the number of times 'value' appears, the sql statement will return this:

TITLE          |   DESCRIPTION                  |   COUNT
------------------------------------------------------------
test1          |   value blah blah value        |   2
test2          |   value test                   |   1
test3          |   test test test               |   0
test4          |   valuevaluevaluevaluevalue    |   5

Is there any way to do this? I do not want to use php at all, just mysql.

 Answers

93

This should do the trick:

SELECT 
    title,
    description,    
    ROUND (   
        (
            LENGTH(description)
            - LENGTH( REPLACE ( description, "value", "") ) 
        ) / LENGTH("value")        
    ) AS count    
FROM <table> 
Tuesday, June 1, 2021
 
dotoree
answered 7 Months ago
74

From the PHP Manual on mysqli_stmt::execute:

mysqli_stmt::execute -- mysqli_stmt_executeExecutes a prepared Query

Returns TRUE on success or FALSE on failure.


if ($stmt->execute()) { // exactly like this!
    $success = true;
}

You're doing it right... What's your dilemma?

Wednesday, March 31, 2021
 
Sauleil
answered 9 Months ago
20

I have updated this answer. I like the idea of using a match better, but it is slower:

console.log(("str1,str2,str3,str4".match(/,/g) || []).length); //logs 3

console.log(("str1,str2,str3,str4".match(new RegExp("str", "g")) || []).length); //logs 4

Use a regular expression literal if you know what you are searching for beforehand, if not you can use the RegExp constructor, and pass in the g flag as an argument.

match returns null with no results thus the || []

The original answer I made in 2009 is below. It creates an array unnecessarily, but using a split is faster (as of September 2014). I'm ambivalent, if I really needed the speed there would be no question that I would use a split, but I would prefer to use match.

Old answer (from 2009):

If you're looking for the commas:

(mainStr.split(",").length - 1) //3

If you're looking for the str

(mainStr.split("str").length - 1) //4

Both in @Lo's answer and in my own silly jsperf test split comes ahead in speed, at least in Chrome, but again creating the extra array just doesn't seem sane.

Tuesday, June 1, 2021
 
Lance
answered 7 Months ago
33

You can use the following code:

String in = "i have a male cat. the color of male cat is Black";
int i = 0;
Pattern p = Pattern.compile("male cat");
Matcher m = p.matcher( in );
while (m.find()) {
    i++;
}
System.out.println(i); // Prints 2

Demo

What it does?

It matches "male cat".

while(m.find())

indicates, do whatever is given inside the loop while m finds a match. And I'm incrementing the value of i by i++, so obviously, this gives number of male cat a string has got.

Friday, June 18, 2021
 
fardjad
answered 6 Months ago
69

The problem is covered on the github issues thread - https://github.com/flask-admin/flask-admin/issues/1588

Basically, the flask-admin pip package is out of date, in regards the latest sqlalchemy pip package. In that specific area,

cls, key = identity_key(instance=obj)

sqlalchemy is now returning 3 objects, but flask-admin is only expecting 2, hence the error.

The real solution for this is to wait until a new flask-admin version is uploaded to pip, until then, you've a few options.

  1. Manually go in and edit that fields.py file
  2. As detailed in that issues thread, limit sqlalchemy to version 1.2.0b3. You can do this in your requirements.txt file, or manually with a pip upgrade install, pip install --upgrade sqlalchemy==1.2.0b3
  3. As the fix is in flask-admin's master branch in their github repository, install that version of flask-admin with the pip location of git+https://github.com/flask-admin/flask-admin. Again, you do this in your requirements.txt file, or with a pip upgrade install, pip install --upgrade git+https://github.com/flask-admin/flask-admin.

My personal preference, and what I've done, is option 3. If you look through the code itself, it's a line the maintainers want to remove, anyway, and how they're dealing with it is better, and in general with these things, I prefer to go forward (latest version of flask-admin) rather than holding things back (rollback sqlalchemy to a previous version), and certainly better than manually editing the raw code.

Tuesday, August 3, 2021
 
Viralk
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share