So I've looked for an answer to this question on the web but couldn't find anything that covers this specifically.
Our database stores the project version in the format #.#.# (Rev_#) so there is both text and numbers in the string. I have no control over the data so I am stuck with this format. Our users have the need to retrieve a range of these projects and the specific problem has to do with the rev portion. If they want to retrieve something like 10.1.100 (Rev_2) to 10.1.100 (Rev_11) they will get no results as 1 is less than 2 when treated as a string. Is there a way to write a general SQL statement that will handle this condition? I would prefer to not refer to "Rev" in the statement so that I can keep this general if possible. If that's not possible I could do that though as a last resort.
Any help would be appreciated. I know the basics of SQL queries but can't write anything too complicated.