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
  • 313
  • 1
  • 7
  • 15

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
  • 47,252
  • 12
  • 78
  • 118
Dimitri Dewaele
  • 10,311
  • 21
  • 80
  • 127
  • 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