如何查询以查找字符串中的特定文本并在其旁边抓取部分？

Question

Trying to understand LEFT and LOCATE with mysql to help me process a string

I have text that contains a bunch of data and within it is

street_num="9716", street_name=

I need to extract just the street num

so I was trying to do

SELECT LEFT( newdata, LOCATE( 'street_name=', 'newdata' ) ) 
FROM `uploadTracker` 
WHERE `type` =0

in that example I would like it to return 9716

Answer 1

In situations like this function SUBSTRING_INDEX() becomes very handy

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(newdata, 'street_num="', -1), '"', 1) street_num
  FROM uploadTracker
 WHERE type = 0

Output:

| STREET_NUM |
|------------|
|       9716 |

Here is SQLFiddle demo