Extract data to the left of a specified character 1

[elsenorjose]

Hello all,

Using SQL Server 2005, I'm trying to extract only the string to the left of the second '/'.

John Smith /Data Analyst /Corporate /Analysts /North America /JSMITH

I just want to retrieve 'John Smith / Data Analyst'.

The length of the string will always vary, but I will always only want the data up to the 2nd '/'.

Is this possible using a simple CASE? I've seen functions to do this but I'd really like to avoid that.

Thank you.

 

[gbaughma]

Print SUBSTRING('John Smith/Data Analyst/Something',1,CHARINDEX('/','John Smith/Data Analyst/Something',CHARINDEX('/','John Smith/Data Analyst/Something')+1)-1)

The stacked CHARINDEX is to find the SECOND /

The output is:

John Smith/Data Analyst

Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[bborissov]

declare @Test varchar(200)
SET @Test = 'John Smith /Data Analyst /Corporate /Analysts /North America /JSMITH'

SELECT LEFT(@test,CHARINDEX('/',@Test,CHARINDEX('/',@test)+1)-1)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Be careful with strings that don't have any slashes. You'll probably get an error. You might also want to use patindex and charindex. Patindex has am optional third parameter for starting position. So, you could use charindex as the third parameter of the patindex function.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[elsenorjose]

Thanks Greg. Perfect!

 

[gbaughma]

Yay! I done good for once!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[markros]

Take a look at this blog

http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html

for an interesting CROSS APPLY technique to get the needed info.

PluralSight Learning Library