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

Find last occurence of a character within a string Expand / Collapse
Author
Message
Posted Wednesday, April 8, 2009 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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)>0
begin
set @lastpos=@pos
set @pos=charindex('\',FieldA,@pos+1)
end

SUBSTRING(FieldA ,@pos + 1, LEN(FieldA) - (@pos + 1)) END AS FileUploaded
FROM TableA

Example data of FieldA : "DirectoryA\DirectoryB\DirectoryC\FileName"
"DirectoryA\DirectoryB\FileName"

Thank you.
Post #693069
Posted Wednesday, April 8, 2009 7:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 Simmons
http://twitter.com/KenSimmons
Post #693092
Posted Wednesday, April 8, 2009 7:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
Quickest way is to reverse the string and look for the first occurrence.

DECLARE @StringA varchar(100), @pos int
SET @stringA = 'DirectoryA\DirectoryB\FileName'

-- Where is the last '\'?

SET @pos = LEN(@StringA) - CHARINDEX('\',REVERSE(@StringA))
SELECT @pos




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #693100
Posted Wednesday, April 8, 2009 7:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:52 AM
Points: 2,551, Visits: 2,594
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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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 Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers



Post #693128
Posted Wednesday, April 8, 2009 8:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 Filename
FROM (
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Thank you Ken Simmons, that worked. Have a nice day.
Post #693171
Posted Tuesday, May 4, 2010 5:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse