In MySQL, Find Strings with a Given Prefix

In MySql, find strings with a given prefix

Surprisingly, a LIKE query will use an index just fine if you're doing a prefix search.

SELECT * from TableName Where ColumnName LIKE 'o hai%'

will indeed use an index since it does not begin with a wildcard character.

This (and other behavior) is documented in the "How MySQL uses Indexes" doc:
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

You will need to escape the '%' character and follow normal quoting rules, but other than that any utf-8 input prefix ought to work and do the job. Run an EXPLAIN query to make sure, sometimes other reasons can preclude indexes from working such as needing to do an OPTIMIZE TABLE to update index cardinalities (though this can take ages and locks your table)

MySQL SELECT row based on prefix

You may phrase your LIKE expression in the reverse order:

SELECT *
FROM table1
WHERE '123456' LIKE CONCAT(keyCode, '%');

This would compare, for example, '123456' against '1234%', which should be a match in this case.

How to select the strings that only begin with specific prefixes

You can do it using the below query:

SELECT PN FROM MyTestTable WHERE SUBSTR(PN,1,2) IN ('AC','AY','AN','AZ','IR')

I hope this work for you .

Search strings that have a specific prefix xxx*

Just use the LIKE operator:

SELECT * FROM mytable WHERE date_id LIKE '111-22%'

How to find groups of strings in MySQL that share the same (unkown) prefixes?

Try the MySQL SUBSTRING function:

select SUBSTRING(email,1,5),count(*) from users group by 1 having count(*)>2 

Prefix best match MySQL

One option is to have a sub-query that gives you the longest prefix matching provider_id and prefix. Something like this:

select domain_id from routing_domain
where
  provider_id = 9
  and '39321xxxxxxx' like concat(prefix, '%')
  and length(prefix) =
  (    select max(length(prefix))
       from routing_domain
       where 
         provider_id = 9
         and '39321xxxxxxx' like concat(prefix, '%')
  )

See my fiddle here.

---

Related Topics