Need SQL function to get part of sring

Posted on 2011-10-07
Last Modified: 2012-05-12

using Microsoft SQL Server 2005. I need a SQLfunction or statement to get part of a varchar.

All the values will have 2 dashes '-' like this:

AZ - District of Arizona - Phoenix
CA - Central District of California - San Fernando Valley
FL - Northern District of Florida - Tallahassee

I need to get everything to the left of the second dash without the space before it.  So I would need

AZ - District of Arizona<no space>
CA - Central District of California
FL - Northern District of Florida

somewhat new I have no idea any help please
Question by:charlesbaldo
    LVL 21

    Expert Comment

    You don't need a special function, just some string manipulation.  You could do something like this.

    SELECT SUBSTRING(YourColumn, CHARINDEX('-', YourColumn) + 2, CHARINDEX('-', YourColumn, CHARINDEX('-', YourColumn) + 1) - CHARINDEX('-', YourColumn) - 2)


    LVL 25

    Accepted Solution

    select reverse(right(reverse(mycolumn), len(mycolumn) - charindex('-', reverse(mycolumn)) - 1)) from mytable

    It works. May not be the best.
    LVL 21

    Assisted Solution

    Oops.  I thought you were trying to extract the text between the dashes.  lsavidge's solution works.  There are actually a number of ways to do it.  Here's another.

    SELECT LEFT(@String, CHARINDEX('-', @String, CHARINDEX('-', @String) + 1) - 2)



    Author Closing Comment

    Thanks All

    Featured Post

    Highfive Gives IT Their Time Back

    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 having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    In this article I will describe the Copy Database Wizard 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.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    732 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

    25 Experts available now in Live!

    Get 1:1 Help Now