Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove characters after last slash in string Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 12:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:08 AM
Points: 1,380, Visits: 2,703
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
Post #1434757
Posted Monday, March 25, 2013 12:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 20,803, Visits: 32,731
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));





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1434761
Posted Monday, March 25, 2013 12:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:08 AM
Points: 1,380, Visits: 2,703
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

Post #1434762
Posted Monday, March 25, 2013 10:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:57 PM
Points: 546, Visits: 1,062
That's an awful lot like one of my favorite expressions in SSIS... lol
Post #1435014
Posted Monday, March 25, 2013 10:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
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)
Post #1435044
Posted Monday, March 25, 2013 10:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:40 AM
Points: 20,803, Visits: 32,731
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1435046
Posted Monday, March 25, 2013 11:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
Agreed, I am just being lazy :p
Post #1435057
Posted Monday, March 25, 2013 11:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
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)
Post #1435066
Posted Monday, March 25, 2013 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 13,228, Visits: 12,705
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1435090
Posted Monday, March 25, 2013 3:46 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:06 AM
Points: 2,268, Visits: 3,425
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1435188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse