[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Need SQL function to get part of sring

Posted on 2011-10-07
Medium Priority
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:Charles Baldo
  • 2
LVL 21

Expert Comment

ID: 36930841
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

Lee Savidge earned 1400 total points
ID: 36930848
select reverse(right(reverse(mycolumn), len(mycolumn) - charindex('-', reverse(mycolumn)) - 1)) from mytable

It works. May not be the best.
LVL 21

Assisted Solution

JestersGrind earned 600 total points
ID: 36930921
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

by:Charles Baldo
ID: 36931785
Thanks All

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

834 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