0

I have a MySQL table and I need to retrieve some fields from it. I need the string that contains H_ and HP_ Separately using two different query.

But the problem is the underscore is in the wildcard and i am unable to find correct result.

Example Query of HP_

SELECT nc.`logname`, nc.`client_id`, nc.`Customer_Name`, cb.`bwm_day_cir_upload`, cb.`bwm_day_cir_download` 
FROM `new_client` nc
LEFT JOIN `client_bandwidth` cb ON cb.`client_id` = `nc`.`client_id`
WHERE nc.`logname` LIKE '%HP_%' AND nc.`Active` = 'y' ORDER BY TRIM(UPPER(nc.`logname`))
Murad Hasan
  • 9,565
  • 2
  • 21
  • 42
  • 1
    Escape it `\_` . – Lukasz Szozda Aug 16 '17 at 06:38
  • 1
    And when you run into problems like this, it is always worth to first check the documentation. [MySQL documentation for `like`](https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like): _"To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the `ESCAPE` character, `\\` is assumed."_ – Mark Rotteveel Aug 16 '17 at 06:40
  • @MarkRotteveel, Thanks for the suggestion. – Murad Hasan Aug 16 '17 at 06:42

2 Answers2

1

As You told it's a wildcard. Then just escape it with '\_'

SELECT nc.`logname`, nc.`client_id`, nc.`Customer_Name`, cb.`bwm_day_cir_upload`, cb.`bwm_day_cir_download` 
FROM `new_client` nc
LEFT JOIN `client_bandwidth` cb ON cb.`client_id` = `nc`.`client_id`
WHERE nc.`logname` LIKE '%HP\_%' AND nc.`Active` = 'y' ORDER BY TRIM(UPPER(nc.`logname`))
Whencesoever
  • 2,218
  • 15
  • 26
1

Documentation to the rescue! Did you read the following page: https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like

Here's an excerpt:

To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, \ is assumed.

  • \% matches one % character.

  • \_ matches one _ character.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828