0

I am looking for a code which can search a specific column, that always start with a value (ukinvoice) and ends with a numeric value which is greater than 00030 e.g. ukinvoice1245345.inv.1245788945.3.00030

satya
  • 303
  • 1
  • 5
  • 14

1 Answers1

0

Search for a string with the 'like' operator and the % wildcard. Cast the last part of the string to an integer and work with numbers.

SELECT *  
FROM TABLE_NAME
WHERE COLUMN_NAME like 'prefix%'
  AND CAST(SUBSTRING(COLUMN_NAME, (LENGTH(COLUMN_NAME) - 4) , 5) AS INT) > 30;

In writing:

  • Search for all strings with the correct prefix

  • Search the last prefix >> convert to a number >> make sure that the value is greater than your wanted value

Remark: MySQL uses a CONVERT function, instead of CAST

Good Luck!

Juan Carlos Oropeza
  • 45,789
  • 11
  • 74
  • 113
Dimitri Dewaele
  • 9,771
  • 18
  • 75
  • 123
  • Just take caution if either the prefix or postfix contain any `LIKE` especial characters, there must be escaped: https://stackoverflow.com/q/5821/2557263 – Alejandro Jan 12 '18 at 12:53
  • Thanks Dimitri, but I am looking for an expression where the Prefix is always "ukinvoice" and Postfix always greater than 00030. So i can use this expression to calculate other values in my query – satya Jan 12 '18 at 12:58
  • Hi Satya, I indeed overlooked that part and added an update. good luck! – Dimitri Dewaele Jan 12 '18 at 13:18