1

I have a column of my database which increases when I insert new rows. It doesn't increase numerically but with a mix of letters and numbers. So instead of doing 1 (for first insert), 2 ( for second insert), 3 (for third insert) and so on, it does i_1, i_2, i_3 and so on. I cannot change this method of increasing. Now I would select all the rows after a particular value (for example after the row which contains the value i_2), but I don't know how doing it, because if i do "where column_name>'i_2' it considers the length of the string.

Anyone could help me, please? Many thanks.

Grancein
  • 642
  • 10
  • 19

1 Answers1

3

You can try to replace symbol i_ in following:

WHERE REPLACE(column_name, 'i_', '') > 2

If there will be any problems with comparing numbers you can convert value to INT after replace.

WHERE CAST(REPLACE(column_name, 'i_', '') AS INT) > 2
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • I have used your method and it works. Thank you very much. Could I ask you another thing? If I would have the case in which the i_2 was i_0000002 and it didn't increase as an integer number but as an hexadecimal number, I should do WHERE CAST(REPLACE(column_name, 'i_', '') AS HEX) > 0000002 or I should use the function CONVERT to convert the string in a hexadecimal number? As in this example: http://stackoverflow.com/questions/219245/converting-a-string-to-hex-in-sql – Grancein Sep 29 '15 at 09:48
  • 1
    @GracePii You can try to cast It as `VARBINARY` in following: `CAST(REPLACE(column_name, 'i_', '') AS VARBINARY)` – Stanislovas Kalašnikovas Sep 29 '15 at 10:18