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.

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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: stackoverflow.com/questions/219245/…
@GracePii You can try to cast It as VARBINARY in following: CAST(REPLACE(column_name, 'i_', '') AS VARBINARY)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.