mysql substr not working like php

Question

Please help me resolve my query when using query - I just want to subtract a few characters and then use the % to find the matching LIKE:

select * from `providers` WHERE `name` LIKE SUBSTR('telin',1,4)%

Please let me know what i'm doing wrong, any kind of help is greatly appreciated!

What is the source of 'telin'? Does that come from a variable in PHP? — Michael Berkowski
– Michael Berkowski, Commented Apr 9, 2013 at 17:34

Michael Berkowski · Accepted Answer · 2013-04-09 17:28:04Z

3

Assuming telin is a column name rather than the literal string, it should be quoted in backticks. If it is the literal string, then there is obviously no need to extract a substring from it. I suspect however, that it was the result of a PHP variable you pasted here after echoing out the full query, then it is correctly single-quoted.

Anyway, you will need to concatenate the SUBSTR() result onto the '%' via CONCAT():

SELECT * FROM `providers` WHERE `name` LIKE CONCAT(SUBSTR(`telin`,1,4), '%');

But better would be to use LEFT() to compare the first 4 characters of each:

SELECT * FROM `providers` WHERE LEFT(`name`, 4) = LEFT(`telin`,4);

answered Apr 9, 2013 at 17:28

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tadman Over a year ago

Queries like this create a temporary table, so they don't scale very well for larger amounts of data. So long as you're only dealing with thousands of rows it will be fine.

Collectives™ on Stack Overflow

mysql substr not working like php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related