从Mysql中的字符串属性中提取Integer值

I have an attribute in MYSQL table "primaryCamera", which has value as

我在MYSQL表“primaryCamera”中有一个属性,其值为

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

I want to list mobiles greater than or equal to 8MP, what is exact query in MYSQL which works for me.

我想列出大于或等于8MP的移动设备,MYSQL中的确切查询对我有用。

Please help me.

请帮帮我。

2 个解决方案

#1

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

如果primaryCamera列下的数据采用以下格式,则下面给出的查询可能会完成该任务。

Format: Camera X MP.....

格式:相机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.

LOCATE('Camera',@ str)返回字符串Camera的起始索引。

LENGTH('Camera') returns the length of the string Camera which is 6.

LENGTH('Camera')返回字符串Camera的长度为6。

(LOCATE('MP',@str) returns the start index of the string MP.

(LOCATE('MP',@ str)返回字符串MP的起始索引。

Example:

Given,

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

primaryCamera ='相机8 MP,3264 x 2448像素,卡尔蔡司光学,光学防抖,自动对焦,双'

startIndex of string Camera = 1

字符串Camera = 1的startIndex

Length of Camera = 6

相机长度= 6

start Index of string MP = 10.

开始字符串MP = 10的索引。

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

您要查找的整数值位于此范围内[7,10]。

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

SUBSTRING(字符串FROM START_INDEX_OF_DESIRED_SUBSTRING FOR LENGTH_YOU_WANT_TO_EXTRACT)的工作原理如下。

In order to get that you need to do this:

为了得到你需要这样做:

START_INDEX_OF_DESIRED_SUBSTRING = StartIndex of Camera + Length of Camera

START_INDEX_OF_DESIRED_SUBSTRING =相机的StartIndex +相机长度

LENGTH_YOU_WANT_TO_EXTRACT = Start position of stringMP- START_INDEX_OF_DESIRED_SUBSTRING

LENGTH_YOU_WANT_TO_EXTRACT = stringMP的起始位置 - START_INDEX_OF_DESIRED_SUBSTRING

SUBSTRING(primaryCamera FROM START_INDEX_OF_DESIRED_SUBSTRING FOR LENGTH_YOU_WANT_TO_EXTRACT );

SUBSTRING(从START_INDEX_OF_DESIRED_SUBSTRING到LENGTH_YOU_WANT_TO_EXTRACT的primaryCamera);

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.

注意:您应该将产品的属性(例如,相机)存储在不同表格下的不同列中。否则,即使是最简单的任务,您很快就会接受许多繁琐的任务。

#2

use sub string, the syntax is :

使用子字符串,语法是:

SELECT SUBSTR('string',start,length)

replace string here with your query

用您的查询替换此处的字符串

#1