Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Find last occurence of a character within a string Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 8, 2009 7:27 AM
 Old Hand Group: General Forum Members Last Login: Friday, December 11, 2015 12:22 PM Points: 381, Visits: 690
 Hi All, I am trying to find the last occurence of a character within a string. I have the following code: what is the easiest way to accomplish this?SELECT CASE WHEN FieldA IS NULL THEN '' ELSE set @pos = charindex('\',FieldA,@pos)while(@pos)>0beginset @lastpos=@posset @pos=charindex('\',FieldA,@pos+1)end SUBSTRING(FieldA ,@pos + 1, LEN(FieldA) - (@pos + 1)) END AS FileUploadedFROM TableAExample data of FieldA : "DirectoryA\DirectoryB\DirectoryC\FileName" "DirectoryA\DirectoryB\FileName"Thank you.
Post #693069
 Posted Wednesday, April 8, 2009 7:44 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, May 9, 2013 8:07 AM Points: 1,220, Visits: 2,614
 You can use the Reverse Function to find the last occurrance.Declare @FieldA varchar(100)Set @FieldA = 'DirectoryA\DirectoryB\DirectoryC\FileName'Select Substring(@FieldA, LEN(@FieldA) - Charindex('\',Reverse(@FieldA))+2, LEN(@FieldA)) Ken Simmonshttp://twitter.com/KenSimmons
Post #693092
 Posted Wednesday, April 8, 2009 7:49 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 10:55 AM Points: 45,613, Visits: 44,143
 Quickest way is to reverse the string and look for the first occurrence. `DECLARE @StringA varchar(100), @pos intSET @stringA = 'DirectoryA\DirectoryB\FileName'-- Where is the last '\'?SET @pos = LEN(@StringA) - CHARINDEX('\',REVERSE(@StringA))SELECT @pos` Gail ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #693100
 Posted Wednesday, April 8, 2009 7:52 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 7, 2016 5:15 AM Points: 2,562, Visits: 2,643
 Or this way too...`DECLARE @SomeFile VARCHAR(255)SET @SomeFile = 'C:\WINDOWS\system32\FileName.xls'SELECT RIGHT( @SomeFile, CHARINDEX( '\', REVERSE( @SomeFile ) + '\' ) - 1 ) ` --Ramesh
Post #693102
 Posted Wednesday, April 8, 2009 8:04 AM
 SSCommitted Group: General Forum Members Last Login: Monday, October 25, 2010 6:09 AM Points: 1,621, Visits: 409
 Ramesh (4/8/2009)Or this way too...`DECLARE @SomeFile VARCHAR(255)SET @SomeFile = 'C:\WINDOWS\system32\FileName.xls'SELECT RIGHT( @SomeFile, CHARINDEX( '\', REVERSE( @SomeFile ) + '\' ) - 1 ) `Perfect solution..with simple trick using REVERSE... Thanks -- Vijaya Kadiyalawww.dotnetvj.comSQL Server Articles For Beginers
Post #693128
 Posted Wednesday, April 8, 2009 8:08 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, October 24, 2012 2:12 PM Points: 1,212, Visits: 3,232
 If you're looking to retrieve the filename from a path, then this seems to do the job:`SELECT FieldA, COALESCE(RIGHT(FieldA, NULLIF(CHARINDEX('\', REVERSE(FieldA)) - 1, -1)), FieldA, '') AS FilenameFROM ( SELECT 'DirectoryA\DirectoryB\DirectoryC\FileName1' AS FieldA UNION ALL SELECT 'DirectoryA\DirectoryB\FileName2' UNION ALL SELECT 'FileName3' UNION ALL SELECT 'DirectoryA\DirectoryB\' UNION ALL SELECT '' UNION ALL SELECT NULL) TableA`
Post #693136
 Posted Wednesday, April 8, 2009 8:28 AM
 Old Hand Group: General Forum Members Last Login: Friday, December 11, 2015 12:22 PM Points: 381, Visits: 690
 Thank you Ken Simmons, that worked. Have a nice day.
Post #693171
 Posted Tuesday, May 4, 2010 5:48 PM
 SSC Rookie Group: General Forum Members Last Login: Monday, January 16, 2012 12:55 PM Points: 34, Visits: 688
 Nice little bit of Code, Thanks!
Post #915751

 Permissions