mysql match string with start of string in table

Question

I realise that it would be a lot easier if I could modify the table when it was created, but assuming I can't, I have a table that is such as:

abcd
abde
abdf
abff
bbsdf
bcggs
... snip large amount
zza

The values in the table are not fixed length. I have a string to match such as abffagpokejfkjs . If it was the other way round, I could do

SELECT * from table where value like 'abff%'

but I need to select the value that matches the start of a string that is provided.

Is there a quick way of doing that, or does it need an itteration through the table to find a match?

I don't understand the problem. What value are you trying to return? Why can you not just use LEFT(value, 4) or a LIKE? — Michael Berkowski
– Michael Berkowski, Commented Mar 12, 2012 at 12:48
I can't use LEFT as I don't know how many characters the result is - could be 1, could be 25. Could loop through all the numbers but doesn't sound fast. — Woody
– Woody, Commented Mar 12, 2012 at 12:54

Mark Byers · Accepted Answer · 2012-03-12 12:49:10Z

51

Try this:

SELECT col1, col2 -- etc...
FROM your_table
WHERE 'abffagpokejfkjs' LIKE CONCAT(value, '%')

Note that this will not use an index effectively so it will be slow if you have a lot of records.

Also note that some characters in value (e.g. %) may be interpreted by LIKE as having a special meaning, which may undesirable.

answered Mar 12, 2012 at 12:49

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

Comments

ToolmakerSteve · Accepted Answer · 2020-05-07 23:53:27Z

18

LIKE can be avoided, by truncating the comparison string to each value's length:

... WHERE LEFT('abffagpokejfkjs', LENGTH(value)) = value

answered May 7, 2020 at 23:53

ToolmakerSteve

22.2k16 gold badges114 silver badges233 bronze badges

I had to use CHAR_LENGTH. LENGTH returns number of bytes, which in case of non-ASCII strings may not be desired.