SUBSTRING_INDEX problem

[smagdy]

Hello,

I want to select a row depending on SUBSTRING value of a field but it doesnt work.. or i dono how to do it.

select id from table where SUBSTRING_INDEX('0.3 MP camera', ' M', 1) = '0.3'

how to let this work?

Thanks in advance

[dreamcatcher]

Hi smagdy,

You are specifying a count of 1 which means that all this would return is 0

Try simply using SUBSTRING as you don`t need a delimiter:

select id from table where SUBSTRING('0.3 MP camera', 0, 3) = '0.3'

For more info see the following:
[dev.mysql.com...]

dc

[smagdy]

but where to specify which field to get the substring from?

[smagdy]

select id from table where SUBSTRING('0.3 MP camera', 0, 3) = '0.3'

it extracts the SUBSTRING from which field?

[dreamcatcher]

Try something like this:

SELECT id,SUBSTRING('tablename', 0, 3) as name FROM table WHERE name = '0.3';

or maybe:

SELECT id,SUBSTRING('tablename', 0, 3) FROM table WHERE tablename = '0.3';

One of those should work.

dc

[smagdy]

No, it wont work like that because i ve a variable that contains something like 0.3 or 0.5 and ive text field in mysql that have somethin like 0.3 MP so all i wana do is to select the row that contains a field which its substring from pos 1 untill 'M' equal to my variable.

hope am clear!

thanks

[coopster]

Then the first thing you need to figure out is where the "M" is ...

LOCATE('M', myColumn)

... and then use that as the length of the substring to return for comparison:

SELECT 
 myColumn 
 FROM myTable 
 WHERE  
 SUBSTRING(myColumn, 1, LOCATE('M', myColumn) - 1) = '$myVariable' 
;

However, if you are going to go through all that, why not just use LIKE?

SELECT 
 myColumn 
 FROM myTable 
 WHERE myColumn LIKE '$myVariable M%' 
'

[smagdy]

Thanks a lot, this one worked as i want

SELECT
myColumn
FROM myTable
WHERE myColumn LIKE '$myVariable M%'

but i think uve mistaken the last "myColumn" .. it should be "myField"

thanks