0

I have an SQL query which matches results using a LIKE :

_column_name_%

This will return results where the column name is:

_column_name_1
_column_name_2

The end number could be a really high number e.g. 32523, or even 523624366234.

If I use _column_name_%%%%% this would match 32523, but not 523624366234.

How would I get the LIKE to match without typing % repeatedly?

kuma DK
  • 1,812
  • 1
  • 18
  • 36
bigdaveygeorge
  • 947
  • 2
  • 12
  • 32
  • 1
    Your assumption of how `Like` wildcards work is incorrect. `%` is a wildcard for any number of characters. You don't have to repeat it at all - one `%` is sufficient. `Where Column Like '_column_name_%'` is perfectly fine. – Siyual Mar 24 '17 at 18:55
  • 1
    You don't need to type `%` repeatedly. One is enough. It matches zero or more characters – Gurwinder Singh Mar 24 '17 at 18:55
  • Can you show me an example, this only returns any row with _column_name_1 but not _column_name_11 for me. – bigdaveygeorge Mar 24 '17 at 18:56
  • 1
    Just curiosity: are you speaking about columns names or values in a field? – Lelio Faieta Mar 24 '17 at 18:56
  • give the full complete query you are working on – kuma DK Mar 24 '17 at 18:57
  • @bigdaveygeorge Please show *exactly* how you're querying the data as well as some sample data. What you're describing does not sound possible. – Siyual Mar 24 '17 at 18:57
  • Sorry this is a column called 'meta_key' and the value is 'column_name_[the number]' – bigdaveygeorge Mar 24 '17 at 18:57
  • 1
    @bigdaveygeorge `Where meta_key Like 'column_name_%'` should match any size number on the end. If it is not matching certain columns, you may want to look at those particular records and make sure there aren't any spaces or unprintable characters at the *beginning* of the column. The lack of the leading `%` in the `Like` will filter out anything that doesn't literally start with `column_name_` – Siyual Mar 24 '17 at 18:58
  • Possible duplicate of [MySQL LIKE query with underscore](https://stackoverflow.com/questions/22167132/mysql-like-query-with-underscore) – Roham Rafii Jan 21 '19 at 13:54

1 Answers1

2

A Simple select query with the LIKE operator should look like this

You have to escape the underscore using "\" if you are having any.

instead of pretext_% use pretext\_%

Select * from mytable where field_1 like 'pretext\_%'

This will return pretext_1 as well as pretext_11

kuma DK
  • 1,812
  • 1
  • 18
  • 36