Want to return character(s) after a dash (-)

Question

Post reply

Want to return character(s) after a dash (-)

david.dartnell

Ten Centuries

Points: 1276
More actions
March 23, 2015 at 10:15 pm

#302339

Hi Everyone,

I want to return the character(s) in a string directly after a dash (-) character. For example in the following numbers 72187-1 & 72187-2 I am interested in returning 1 & 2 respectively.

It is possible in my scenario that the number after the dash is two digits long, e.g.: 72187-12 in which case I want to return 12.

Any help here will be greatly appreciated.

Kind Regards,

David

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

I used a temp variable in my solution below for sample data so that you can just run this in SSMS. The SELECT statement is what you're looking for.

DECLARE @x TABLE (yourvalue varchar(20));



INSERT @x VALUES ('72187-1'),('72187-2'),('72187-12'),('555666-99999');

-- solution:

SELECT substring(yourvalue,charindex('-',yourvalue)+1, len(yourvalue)-charindex('-',yourvalue))

FROM @x;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183686 More actions · Answer 2

Alan's solution can be simplified by using the length of your column instead of calculations. You could also achieve it with the STUFF function (just for fun).



DECLARE @x TABLE (yourvalue varchar(20));

INSERT @x VALUES ('72187-1'),('72187-2'),('72187-12'),('555666-99999'), ('16548');

-- solution:

SELECT substring(yourvalue,charindex('-',yourvalue)+1, 20),

       STUFF( yourvalue, 1, charindex('-',yourvalue), ''),

--With empty value

       substring(yourvalue,charindex('-',yourvalue + '-')+1, 20),

       STUFF( yourvalue, 1, charindex('-',yourvalue + '-'), '')

FROM @x;

I included a value without a dash. Is the result correct? Or should it return an empty value?

EDIT: Added the formulas for empty value.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86913 More actions · Answer 3

Then there's always the old RIGHT, REVERSE yourvalue trick:



DECLARE @x TABLE (yourvalue varchar(20));

INSERT @x VALUES ('72187-1'),('72187-2'),('72187-12'),('555666-99999');

SELECT *, RIGHT(yourvalue, CHARINDEX('-', REVERSE(yourvalue))-1)

FROM @x;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

You can specify a longer length on the SUBSTRING and it won't hurt anything, so you skip calculating the length; for example:



SELECT SUBSTRING(string_value, CHARINDEX('-', string_value) + 1, 100)

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

ScottPletcher (3/25/2015)

You can specify a longer length on the SUBSTRING and it won't hurt anything, so you skip calculating the length; for example:

SELECT SUBSTRING(string_value, CHARINDEX('-', string_value) + 1, 100)

+100

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 6

I like using parsename... as long as is no more than 4 parts.. 😛

DECLARE @x TABLE (yourvalue varchar(20));



INSERT @x VALUES ('72187-1'),('72187-2'),('72187-12'),('555666-99999');

select yourvalue

,Parsename(REPLACE(yourvalue,'-','.'),1)

from @x

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D