Search code examples
phpmysqlsqlmysqlisql-like

Select specific numeric value only in string

I need to isolate a numeric value in a string when i pull it from the database, a string would typically look something like "blah.blah_123".

I need to select all fields containing fx. 1 at the end but not 10, 100, 123.

Here is a part of my query:

SELECT `table1`.`shipping_code`
FROM `table2`
  INNER JOIN `table1` ON `table1`.`id` = `table2`.`id`
WHERE `table1`.`shipping_code` LIKE '%3%'
Solution

  • Since _ is a LIKE wildcard it needs to be escaped:

    SELECT `table1`.`shipping_code`
FROM `table2`
  INNER JOIN `table1` ON `table1`.`id` = `table2`.`id`
WHERE `table1`.`shipping_code` LIKE '%#_1' escape '#'

    Perhaps MySQL has a default escape character? My answer is (more or less) ANSI SQL compliant, but it should work with MySQL too.