• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 735
  • 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.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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