substring/charindex

Question

Post reply

substring/charindex

kd11

Hall of Fame

Points: 3447
More actions
October 27, 2013 at 6:55 pm

#281711

I would like to pull just the last digits in a sting (after the last "-") which can be either 3 or 4 characters long in a field thats like this:
1) 000006-00005-254-008
2) 010122-00008-141-113
3) 003215-00011-tr-009
4) 000987-87077-tr-989

Viewing 11 posts - 1 through 10 (of 10 total)

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

How about reversing the string and getting everything up to the first "-" and then reverse the result ?

Maybe something like :

declare @s-2 varchar (100) = '000006-00005-254-008'

SELECT REVERSE (SUBSTRING ( REVERSE (@S), 1, CHARINDEX ('-', REVERSE (@S)) - 1))

P Jones SSChampion Points: 12361 More actions · Answer 2

That is just brilliant and I can immediately see a use on our NSN part numbers which are very similar format.

Thanks 🙂

kd11 Hall of Fame Points: 3447 More actions · Answer 3

kd11

Hall of Fame

Points: 3447

October 28, 2013 at 7:34 am

#1661964

Thanks, that is much easier.

sestell1 SSChampion Points: 10230 More actions · Answer 4

You can simplify that a bit by using reverse to find the position of the last "-" from the right, and then using the right function:

declare @s-2 varchar (100) = '000006-00005-254-008'

SELECT REVERSE (SUBSTRING ( REVERSE (@S), 1, CHARINDEX ('-', REVERSE (@S)) - 1))

SELECT Right(@s, CharIndex('-', Reverse(@s))-1)

pmoseley SSC Journeyman Points: 85 More actions · Answer 5

The tough part to me with this kind of situation was always trying to find the character position of the "key" character. Here is a method I've used in the past using the LEN function and subtracting the relative character position of the "key" character.

SUBSTRING(columnname, LEN(columnname) - CHARINDEX('-', REVERSE(columnname)) +2, LEN(columnname) - CHARINDEX('-', columnname))

Hope this helps.

Patrick

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

You can make it even simpler and remove reverse entirely (which can be kind of nasty for performance).

with Something as

(

select '000006-00005-254-008' as SomeValue union all

select '010122-00008-141-113' union all

select '003215-00011-tr-009' union all

select '000987-87077-tr-989'

)

select PARSENAME(REPLACE(SomeValue, '-', '.'), 1)

from Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sestell1 SSChampion Points: 10230 More actions · Answer 7

Nice one Sean.

I always forget about PARSENAME as I never seem to consistently have 3 or less delimiters.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

sestell1 (10/28/2013)
Nice one Sean.
I always forget about PARSENAME as I never seem to consistently have 3 or less delimiters.

Yeah it is somewhat limited but pretty handy when you can use it. It is like one of those single usage tools the mechanics have buried somewhere in the bottom drawer.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

arnipetursson SSCertifiable Points: 6557 More actions · Answer 9

arnipetursson

SSCertifiable

Points: 6557

October 28, 2013 at 2:42 pm

#1662166

Sean,

Very clever use of parsename.

Dwain Camps SSC Guru Points: 86978 More actions · Answer 10

If your trailing digits are always 3 or 4 in length, you could do something like this which avoids to overhead of either PARSENAME or REVERSE.

with Something as

(

select '000006-00005-254-008' as SomeValue union all

select '010122-00008-141-113' union all

select '003215-00011-tr-009' union all

select '000987-87077-tr-9899'

)

SELECT CASE WHEN '-' = LEFT(RIGHT(SomeValue, 4), 1)

THEN RIGHT(SomeValue, 3)

ELSE RIGHT(SomeValue, 4) END

FROM Something;

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