Using SQL - Remove Non-Numeric Text and Find the Greatest Value
Posted on 2012-09-21
Hello Experts –
I have a text field (StandardText012) that is storing a Report Number value. I’m trying to create a SQL expression that will find the greatest value in that StandardText012 column and then add 1. The problem is, users have been entering alpha numeric characters. For example, they have entered “55 (Initial and Final)”. In order to remove the non-numeric characters, I have created the following function:
CREATE Function [dbo].[RemoveNonNumericCharacters](@Temp VarChar(1000))
While PatIndex('%[^0-9]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
Then, my SQL lookup looks like this:
Select Max(dbo.RemoveNonNumericCharacters (StandardText012)) +1 as "Report Number"
However, the “max” here seeems to think 2 is greater than 10, so it’s ordering 014, 1, 1234, 145, 235, 48 etc.
What syntax can I use in my select statement to find the greatest value, so that 10 is greater than 2?