The website uses cookies. By using this site, you agree to our use of cookies as described in the Privacy Policy.
I Agree

Selecting multiple substrings from a field in MySQL

Join Stack Overflow to learn, share knowledge, and build your career.

Asked
Viewed 2k times
0

I have a field that is a longtext in MySQL. I'm looking for any instances of 'media' that could be in it, +/- ~10 characters of context. There are usually multiple instances in a single rows' field, so I need to see the context. How can I write a query to do this? I can't even think of where to start.

So what I'm looking at is this:

SELECT field_data_body FROM table WHERE field_data_body LIKE '%media%';
+----------------------------------+
| field_data_body                  |
+----------------------------------+
| ... ode__media_or ... e immediat | 
+----------------------------------+

The field is actually a long string, and I just parsed the actual test value to show the substrings that would match the WHERE clause.

What I actually want to see is all instances of the string media, which in the example above is two, but in other fields could be more. SUBSTR only shows the first instance of media.

Salman A
228k7777 gold badges396396 silver badges489489 bronze badges
asked May 14 '15 at 18:10
user151841user151841
15.2k2828 gold badges9292 silver badges154154 bronze badges
7
1

In mysql you can create a user define function for this like wordcount. You can get help from this UDF.

mysql count word in sql syntax

answered May 18 '15 at 17:02
Mudassar AliMudassar Ali
11611 silver badge44 bronze badges
1
+100

CREATE FUNCTION of your own. Inside the function you can use the WHILE statement and general string functions such as LOCATE and SUBSTRING.

Here is an example to get you started:

DELIMITER $$

CREATE FUNCTION substring_list(
    haystack TEXT,
    needle VARCHAR(100)
)
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE needle_len INT DEFAULT CHAR_LENGTH(needle);
    DECLARE output_str TEXT DEFAULT '';
    DECLARE needle_pos INT DEFAULT LOCATE(needle, haystack);
    WHILE needle_pos > 0 DO
        SET output_str = CONCAT(output_str, SUBSTRING(haystack, GREATEST(needle_pos - 10, 1), LEAST(needle_pos - 1, 10) + needle_len + 10), '\n');
        SET needle_pos = LOCATE(needle, haystack, needle_pos + needle_len);
    END WHILE;
    RETURN output_str;
END$$

DELIMITER ;

Here are some tests. For each match, the term ("media") and up to 10 characters on either side are returned, all concatenated in a single string:

SELECT substring_list('1234567890media12345678immediate34567890media1234567890', 'media');
+---------------------------+
| 1234567890media12345678im |
| 12345678immediate34567890 |
| te34567890media1234567890 |
+---------------------------+
SELECT substring_list('0media12345678immediate34567890media1', 'media');
+---------------------------+
| 0media12345678im          |
| 12345678immediate34567890 |
| te34567890media1          |
+---------------------------+
answered May 20 '15 at 22:33
Salman ASalman A
228k7777 gold badges396396 silver badges489489 bronze badges
0

Here is a solution using PHP that will return each row and each result plus the surrounding characters in a multidimensional array.

$value = "media";
$surroundingChars = 5;
$strlen = strlen($value);

$stmt = $pdo->prepare("SELECT field_data_body FROM table WHERE field_data_body LIKE ?";
$stmt->execute([ '%'.$value.'%' ]);
$result = 0;
while ($body = $stmt->fetchColumn()) {
    $start = 0;
    while (($pos = stripos($body, $value, $start)) !== FALSE) {
         $return[$result][] = substr($body, $pos - $surroundingChars, $strlen + ($surroundingChars * 2));
         // Adjust next start 
         $start = $pos + $strlen;
    }
    $result++;
}

You could always change the $return[$result][] line, but to echo all rows in the format you wanted, you could do this:

foreach($return as $row) {
    echo implode('..', $row);
}

As you stated in the comments, you'd rather a query, but if you change your mind, here is a solution matching your PHP requirements.

answered May 18 '15 at 15:53
DevonDevon
30.2k99 gold badges4545 silver badges7676 bronze badges
1

Your Answer

Sign up or log in

Sign up using Google
Sign up using Facebook
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged mysql sql string or ask your own question.

Measure
Measure
Summary | 1 Annotation
1234
2021/04/07 19:37