Callin all Ghurus - Parsing Question

Question

Callin all Ghurus - Parsing Question

gleazenby

SSC Eights!

Points: 800
More actions
December 18, 2003 at 12:12 pm

#161269

Sample Data:
row1 field1 = peer-324-Leadership Qualities
row2 field1 = peer-3255-Organizational Skills
row3 field1 = peer-32666-Management skills
WHAT I WANT TO DO:
Run a query and get ONLY get the data from field1 that exists after the second "-" and then sort in ascending order.
SAMPLE REQUESTED OUTPUT FROM THE ABOVE DATA:
Leadership Qualities
Management skills
Organizational Skills
Is there a ListGetAt function in SQL - or something comparable?
I am anxiously awaiting a reply from a Ghuru on this one!
Thanks, in advance, for your assistance.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1



SELECT PARSENAME(REPLACE(Col1,'-','.'),1) txt

FROM YourTable

ORDER BY txt

 

SELECT RIGHT(Col1,CHARINDEX('-',REVERSE(Col1))-1) txt

FROM YourTable

ORDER BY txt

--Jonathan

gleazenby SSC Eights! Points: 800 More actions · Answer 2

Johnathan hit the "nail on the head" - beautiful!!

Thank you!

gljjr SSCrazy Eights Points: 9970 More actions · Answer 3

Here is a Single step version.



DECLARE @v varchar(30)

    , @iLastHyphen int

    , @iLocationFromEnd int



SET @v = 'peer-3255-Organizational Skills'



SELECT SUBSTRING(@v,LEN(@v) - (CHARINDEX('-',REVERSE(@v)) - 2), 30)

-- Note: The - 2 is done due to the reversal of the phrase and the way CHARINDEX works.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.