String break using substring and charindex

Question

Post reply

String break using substring and charindex

knakka99

Say Hey Kid

Points: 671
More actions
April 19, 2012 at 5:47 am

#272947

How can we use substring and charindex to seprated the file name from the string? This is a sample of 2 from 15000 records
filename
S:\FORMS\Safety\Fire Extinguishers\ft.miami feb..xls
F:\mp2acc50\data\fire dept steamer conection.bmp
Result need is :
ft.miami feb..xls
fire dept steamer conection.bmp

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

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

This should help you out for time-being.

I think there was a similar post even earlier which had more than one solution, will check and re-post if I get it.

DECLARE @files TABLE

(

filename VARCHAR(1000)

)

INSERT@files

SELECT'S:\FORMS\Safety\Fire Extinguishers\ft.miami feb..xls' UNION ALL

SELECT'F:\mp2acc50\data\fire dept steamer conection.bmp'

SELECT RIGHT( filename, CHARINDEX( '\', REVERSE( filename ), 1 ) - 1 ), filename

FROM@files

Check the below mentioned link as well

The solution suggested by Paul White works with Unicode data as well

http://www.sqlservercentral.com/Forums/FindPost954355.aspx

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

calvo SSChampion Points: 12930 More actions · Answer 2

Here's a substring solution to go with Kingston's DDL. Not sure which is faster.

select

SUBSTRING(filename,len(filename) - charindex('\',reverse(filename),1) + 2,charindex('\',reverse(filename),1))

from @files

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

knakka99 Say Hey Kid Points: 671 More actions · Answer 3

knakka99

Say Hey Kid

Points: 671

April 19, 2012 at 6:51 am

#1475980

Thank you Kingston Dhasian.. for the response

knakka99 Say Hey Kid Points: 671 More actions · Answer 4

Thanks calvo,

This did work fast, however in case when there is not charindex it returned a blank eg. BCGEN.JPG

filename

K:\mp2acc50\data\BOILER INSPECTION DUE DATES.pdf

BCGEN.JPG

S:\Fire Extinguishers\Fire Extinguishers-Care\900 WATERVILLE fire ext..xls

Result

BOILER INSPECTION DUE DATES.pdf

900 WATERVILLE fire ext..xls

knakka99 Say Hey Kid Points: 671 More actions · Answer 5

When there is not charindex it returned a blank eg. BCGEN.JPG what did is use a case statement..

So from what you guys have given me.. I have added an case statement (not sure if this is the correct way but it worked)

DECLARE @files TABLE

(

filename VARCHAR(1000)

)

INSERT@files

SELECT'S:\FORMS\Safety\Fire Extinguishers\ft.miami feb..xls' UNION ALL

SELECT'F:\mp2acc50\data\fire dept steamer conection.bmp' UNION ALL

SELECT 'BCGEN.JPG'

select case when [filename] not like '%\%' then [filename] when [filename] like '%\%' then

SUBSTRING([filename],len([filename]) - charindex('\',reverse([filename]),1) + 2,charindex('\',reverse([filename]),1)) end

from @files

Thank you very much.. Both your replies have been great help to me and also I learned something new

🙂

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

Glad you could solve the issue yourself 🙂

You will learn a few more things if you check the link I referred to in my earlier post

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

knakka99 Say Hey Kid Points: 671 More actions · Answer 7

knakka99

Say Hey Kid

Points: 671

April 19, 2012 at 7:31 am

#1476002

Sure will do.. thank you