Extract String

Question

Extract String

komal145

SSCrazy Eights

Points: 9915
More actions
July 21, 2017 at 11:36 am

#343654

from the given string need to extract the filename:
\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json
How can i get tihis?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

komal145 - Friday, July 21, 2017 11:36 AM
from the given string need to extract the filename:
\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json
How can i get tihis?

With extension or without extension? Will the path be similar or a different number of directories can be part of it?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

komal145 SSCrazy Eights Points: 9915 More actions · Answer 2

Luis Cazares - Friday, July 21, 2017 11:40 AM
komal145 - Friday, July 21, 2017 11:36 AM
from the given string need to extract the filename:
\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json
How can i get tihis?
With extension or without extension? Will the path be similar or a different number of directories can be part of it?

Just file name with extension :vzwprices-2017-06-21.json

komal145 SSCrazy Eights Points: 9915 More actions · Answer 3

komal145 - Friday, July 21, 2017 11:41 AM

Luis Cazares - Friday, July 21, 2017 11:40 AM
komal145 - Friday, July 21, 2017 11:36 AM
from the given string need to extract the filename:
\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json
How can i get tihis?
With extension or without extension? Will the path be similar or a different number of directories can be part of it?

Just file name with extension :vzwprices-2017-06-21.json

will that matters as i need filename ..some files have no extension, i need to get those too.

robin.pryor SSCrazy Points: 2583 More actions · Answer 4

SELECT reverse(left(reverse('\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json'), charindex('\', reverse('\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json')) -1))

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

some similar posts from you
https://www.sqlservercentral.com/Forums/1818860/need-to-extract-text-from-given-string

https://www.sqlservercentral.com/Forums/1882790/Extract-String

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 6

do a reverse lookup on "\", elementary and you'll find many examples around
😎

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

robin.pryor - Friday, July 21, 2017 11:42 AM
SELECT reverse(left(reverse('\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json'), charindex('\', reverse('\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json')) -1))

There's no need to use 2 reverse functions. This is important because this is a slow function.
DECLARE @String varchar(255) = '\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json';

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

komal145 SSCrazy Eights Points: 9915 More actions · Answer 8

Luis Cazares - Friday, July 21, 2017 11:58 AM
robin.pryor - Friday, July 21, 2017 11:42 AM
SELECT reverse(left(reverse('\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json'), charindex('\', reverse('\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json')) -1))
There's no need to use 2 reverse functions. This is important because this is a slow function.
DECLARE @String varchar(255) = '\\CORP-BI-DEV-01\Temp\TIVExplorer\vzwprices-2017-06-21.json';
SELECT RIGHT( @String, CHARINDEX( '\', REVERSE(@String) + '\') - 1);

Thanks it works.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

komal145 - Friday, July 21, 2017 12:21 PM
Thanks it works.

Do you understand why and how it works?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

komal145 SSCrazy Eights Points: 9915 More actions · Answer 10

Luis Cazares - Friday, July 21, 2017 12:24 PM
komal145 - Friday, July 21, 2017 12:21 PM
Thanks it works.
Do you understand why and how it works?

Yes. I was using charindex and right but "reverse" function is much more useful.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Luis Cazares - Friday, July 21, 2017 12:24 PM
komal145 - Friday, July 21, 2017 12:21 PM
Thanks it works.
Do you understand why and how it works?

Took me just a moment to figure out why you concatenated a '\' after you reversed the string. What if there wasn't a leading '\' in the string.