Find last occurence of a character within a string

Question

Post reply

Find last occurence of a character within a string

ramadesai108

SSChampion

Points: 10609
More actions
April 8, 2009 at 7:27 am

#384323

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.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

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))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Or this way too...

DECLARE @SomeFile VARCHAR(255)

SET @SomeFile = 'C:\WINDOWS\system32\FileName.xls'

SELECT RIGHT( @SomeFile, CHARINDEX( '\', REVERSE( @SomeFile ) + '\' ) - 1 )

--Ramesh

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

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

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 5

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

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

Thank you Ken Simmons, that worked. Have a nice day.

David Branscome Old Hand Points: 356 More actions · Answer 7

David Branscome

Old Hand

Points: 356

May 4, 2010 at 5:48 pm

#1162181

Nice little bit of Code, Thanks!