Remove characters after last slash in string

Question

Post reply

Remove characters after last slash in string

SQLSACT

SSC-Insane

Points: 21838
More actions
March 25, 2013 at 12:24 am

#291420

Hi All
I have the following string
declare @string varchar (100)
set @string = 'x:\folder1\folder2\folder3\test.txt'
select @string
How would I remove all the characters after the last '\' in this string?
I need the string to reflect ''x:\folder1\folder2\folder3\'
Any Ideas?
Thanks

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

A bit expensive, but the following works:

declare @string varchar (100)

set @string = 'x:\folder1\folder2\folder3\test.txt'

select @string

select @string,reverse(right(reverse(@string), len(@string) - charindex('\',reverse(@string),1) + 1));

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

Lynn Pettis (3/25/2013)
A bit expensive, but the following works:
declare @string varchar (100)
set @string = 'x:\folder1\folder2\folder3\test.txt'
select @string
select @string,reverse(right(reverse(@string), len(@string) - charindex('\',reverse(@string),1) + 1));

Thank You

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 3

That's an awful lot like one of my favorite expressions in SSIS... lol

raym85 SSC Eights! Points: 938 More actions · Answer 4

if you always know the len of the extnetion this should work as well without being so taxing.

declare @string varchar (100)

set @string = 'x:\folder1\folder2\folder3\test.txt'

select SUBSTRING(@string,1, LEN(@string)-8)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

raym85 (3/25/2013)
if you always know the len of the extnetion this should work as well without being so taxing.
declare @string varchar (100)
set @string = 'x:\folder1\folder2\folder3\test.txt'
select SUBSTRING(@string,1, LEN(@string)-8)

Probably not a good assumption to make.

raym85 SSC Eights! Points: 938 More actions · Answer 6

raym85

SSC Eights!

Points: 938

March 25, 2013 at 11:00 am

#1600435

Agreed, I am just being lazy :p

raym85 SSC Eights! Points: 938 More actions · Answer 7

It bothered me now.

So i had to come up with a way to, but I would prolly choose the first one opver my own.

DECLARE @LastValue varchar(100)

declare @string varchar (100)

set @string = 'x:\folder1\folder2\folder3\test.txt'

SET @LastValue = ( CHARINDEX('\', REVERSE(@string)))

select SUBSTRING(@string,1, (LEN(@STRING) - CONVERT(INT,@LastValue))+1)

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

raym85 (3/25/2013)
It bothered me now.
So i had to come up with a way to, but I would prolly choose the first one opver my own.
DECLARE @LastValue varchar(100)
declare @string varchar (100)
set @string = 'x:\folder1\folder2\folder3\test.txt'
SET @LastValue = ( CHARINDEX('\', REVERSE(@string)))
select SUBSTRING(@string,1, (LEN(@STRING) - CONVERT(INT,@LastValue))+1)

This actually is pretty good. With a little modification you can remove the extra variable.

select SUBSTRING(@string,1, LEN(@STRING) - CHARINDEX('\', REVERSE(@string)) +1)

_______________________________________________________________

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/

ScottPletcher SSC Guru Points: 100942 More actions · Answer 9

And, finally, you don't really need SUBSTRING, LEFT is good enough :-):

SELECT @string, LEFT(@string, LEN(@string) - CHARINDEX('\', REVERSE(@string)) + 1)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.