Need SQL function to get part of sring


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
Charles BaldoSoftware DeveloperAsked:
Who is Participating?
Lee SavidgeCommented:
select reverse(right(reverse(mycolumn), len(mycolumn) - charindex('-', reverse(mycolumn)) - 1)) from mytable

It works. May not be the best.
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)


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)


Charles BaldoSoftware DeveloperAuthor Commented:
Thanks All
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.

All Courses

From novice to tech pro — start learning today.