Using SQL - Remove Non-Numeric Text and Find the Greatest Value
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].[RemoveNonNumericCha
Returns VarChar(1000)
AS
Begin
While PatIndex('%[^0-9]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
Return @Temp
End
Then, my SQL lookup looks like this:
Select Max(dbo.RemoveNonNumericCh
From MyTable
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?
Thank you,
Marcos
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].[RemoveNonNumericCha
Returns VarChar(1000)
AS
Begin
While PatIndex('%[^0-9]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
Return @Temp
End
Then, my SQL lookup looks like this:
Select Max(dbo.RemoveNonNumericCh
From MyTable
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?
Thank you,
Marcos
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
David L. Hansen
It is sorting alpha-numerically (a is before b and 1 is before 2....thus 2 falls after 10 because of the 1. You'll need to use your PatIndex command with a numeric expression as your @Temp instead of VarChar.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I see, I need to convert the text data to an integer. OK, so did this before seeing your responses, and it seem to work great - similar to the "convert" sameer, thank you all! Let me know if I have an error here, but it looks right:
Select Max(CAST(dbo.RemoveNonNume
From MyTable
Select Max(CAST(dbo.RemoveNonNume
From MyTable
ASKER
And, cleaner just to have my function return an Int to begin with, so thank you mankowitz.