Using SQL - Remove Non-Numeric Text and Find the Greatest Value

Posted on 2012-09-21
Last Modified: 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))
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,
Question by:Marcos27
    LVL 10

    Accepted Solution

    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?
    LVL 24

    Assisted Solution

    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

    LVL 15

    Expert Comment

    by: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.
    LVL 5

    Assisted Solution

    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

    Author Comment

    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

    Author Comment

    And, cleaner just to have my function return an Int to begin with, so thank you mankowitz.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now