Extract Integer value from string attribute in Mysql

If the data under primaryCamera column are in the following format then the query given below might do the job.

Format: Camera X MP.....

Query:

SELECT 
primaryCamera
FROM table_name 
WHERE 
CAST(SUBSTRING(@str FROM LOCATE('Camera',@str)+LENGTH('Camera') FOR (LOCATE('MP',@str)-(LOCATE('Camera',@str)+LENGTH('Camera')))) AS UNSIGNED) > 8;

Explanation:

• LOCATE('Camera',@str) returns the start index of the string Camera.
• LENGTH('Camera') returns the length of the string Camera which is 6.
• (LOCATE('MP',@str) returns the start index of the string MP.

Example:

Given,

primaryCamera = 'Camera 8 MP, 3264 x 2448 pixels, Carl Zeiss optics, optical image stabilization, autofocus, dual'

startIndex of string Camera = 1

Length of Camera = 6

start Index of string MP = 10.

The integer value you are looking for lies within this range [7,10].

SUBSTRING(string FROM START_INDEX_OF_DESIRED_SUBSTRING FOR LENGTH_YOU_WANT_TO_EXTRACT) works like this.`

In order to get that you need to do this:

START_INDEX_OF_DESIRED_SUBSTRING = StartIndex of Camera + Length of Camera

LENGTH_YOU_WANT_TO_EXTRACT = Start position of stringMP- START_INDEX_OF_DESIRED_SUBSTRING

SUBSTRING(primaryCamera FROM START_INDEX_OF_DESIRED_SUBSTRING FOR LENGTH_YOU_WANT_TO_EXTRACT );

Note: You should store the attributes of your product (e.g. Camera) in different columns under different table. Otherwise you are soon going to embrace lot of cumbersome tasks to process even for the simplest task.

use sub string, the syntax is :

SELECT SUBSTR('string',start,length)

replace string here with your query