• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

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].[RemoveNonNumericCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
    While PatIndex('%[^0-9]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^0-9]%', @Temp), 1, '')
    Return @Temp

Then, my SQL lookup looks like this:

Select Max(dbo.RemoveNonNumericCharacters (StandardText012)) +1 as "Report Number"
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,
3 Solutions
Could you try to use an implicit conversion to convert your text number string to a number, e.g. multiply the 'number' by 1 and see if SQL Server will allow the implicit conversion from text to number?

Alternatively, could you try using CAST to cast the value as a number?
can you change the return type to a numeric, such as integer?

CREATE Function [dbo].[RemoveNonNumericCharacters](@Temp VarChar(1000)) 
Returns int 

Open in new window

David L. HansenProgrammer AnalystCommented:
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Simply convert the return value of your function to integer and you should be good

try this

Select Max(convert(integer, dbo.RemoveNonNumericCharacters (StandardText012))) +1 as "Report Number"
From MyTable

Let me know if you still have questions
Marcos27Author Commented:
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.RemoveNonNumericCharacters (StandardText012) as Int) +1) as "BPDR"
From MyTable
Marcos27Author Commented:
And, cleaner just to have my function return an Int to begin with, so thank you mankowitz.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now