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
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