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

问题描述：

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

尝试用mysql理解LEFT和LOCATE以帮助我处理字符串 p>

我的文字包含大量数据，其中包含 p>

street_num =“9716”，street_name = p>

我需要提取的只是街道数 p>

所以我试图做 p>

  SELECT LEFT（newdata，LOCATE（'street_name ='，'newdata'））  
FROM`uploadTracker` 
WHERE`type` = 0 
  code>  pre> 
 
 在该示例中我希望它返回9716  p> 
  div>

答

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