T-SQL Help

Posted on 2011-10-13
Last Modified: 2012-05-12
I need to pull the last value from the field.. How do i do this? All three numbers are in the same field. The data type is nvarchar.. I want to pull out the 1799.. NO $ just the 1799.. Some of the fields could have $800 and I want only 800.. I know the code below works for the first value(599) but how do I extract the last?


Open in new window

Question by:cheryl9063
    LVL 59

    Expert Comment

    by:Kevin Cross
    If you want to use that code, you can just reverse the string and check for the first CHARINDEX() and pull RIGHT() of the string that many characters minus 1 for the ',' itself. Other approaches might include a split string function, but given you are comfortable with what you have and the structure, you could stick with that minor adjustment.
    LVL 59

    Accepted Solution

    Here is an example:
    DECLARE @grade2 VARCHAR(25);
    SET @grade2 = '$599,$660,$1799';
    SELECT RIGHT(@grade2, CHARINDEX(',', REVERSE(@grade2))-2)
         , CONVERT(INT, CONVERT(MONEY, RIGHT(@grade2, CHARINDEX(',', REVERSE(@grade2))-1)));

    Open in new window

    If there will always be ',$', then -2 works just fine. If the '$' may or may not exist, you can try something similar to what I showed -- adjusting as needed. It converts to MONEY which handles the '$' in conversion and then convert back to INT or if decimal places allowed DECIMAL|NUMERIC.

    Hope that helps!
    LVL 1

    Author Closing Comment


    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    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…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    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

    18 Experts available now in Live!

    Get 1:1 Help Now