-1

I have to perform this query

select * from mytable where number like '%11%

where number is a numeric field. I found the solution for Postgres doing this:

select * from mytable where CAST(number as text) like '%11%

But if i try to execute this query in Oracle or MySQL, i have an error. I need a

cross-db instruction that do this.

Domenico
  • 231
  • 1
  • 2
  • 6
  • Do you have any code you have tried? – Nickz2 Jul 17 '15 at 10:30
  • 1
    But why do you want to do LIKE on a number? – jarlh Jul 17 '15 at 10:33
  • I have to verify if an order number (that is a numeric field) contains a specific number – Domenico Jul 17 '15 at 10:37
  • Most DBMSes support a version of a MODULO operator, combined with integer division this can be used to extract digits. Can you show some actual data and how you want to process it? – dnoeth Jul 17 '15 at 10:48
  • @Domenico . . . Please provide examples of what you are looking for. `LIKE` works on numeric values. And you can convert numbers to strings. – Gordon Linoff Jul 17 '15 at 11:26
  • `number_column::text LIKE '%123%'` –  Jul 17 '15 at 11:32
  • I have to perform this query select * from mytable where number like '%11% where number is a numeric field. I found the solution for Postgres doing this: select * from mytable where CAST(number as text) like '%11%' But if i try to execute this query in Oracle or MySQL, i have an error. I need a cross-db instruction that do this. – Domenico Jul 17 '15 at 12:00
  • `where CAST(number as varchar) like '%11%` should work with Oracle and Postgres. No Idea about MySQL –  Jul 17 '15 at 12:44
  • Exactly, it works on Oracle and Postgres but not in MySQL!!! – Domenico Jul 17 '15 at 12:47

1 Answers1

0

You can use LIKE operator like this:

CAST(intField AS varchar(50)) LIKE '%' + CAST(intValue AS varchar(50)) + '%'

Other solutions like using modulo need a recursive query based on the length of your intField and your intValue like this example:

intValue = 234  &  max_length_intField = 6

(intField Between 234000 AND 234999) OR
(FLOOR(intField / 10) Between 23400 AND 23499) OR
(FLOOR(intField / 100) Between 2340 AND 2349) OR
(FLOOR(intField / 1000) = 234)
shA.t
  • 16,580
  • 5
  • 54
  • 111
  • @Domenico As I know in some db you need to use `||` instead of `+` or using `CONCAT` function ;). – shA.t Jul 17 '15 at 11:59
  • `||` is the concatenation operator defined by the SQL standard. –  Jul 17 '15 at 12:09