SubString Help

Question

Post reply

SubString Help

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 19, 2010 at 12:04 pm

#239176

Hello Everyone
I am trying to write a backup process that spans domains. Unfortunately I cannot use Log Shipping due to the horrible network architecture.
I have the backup path from the backupset table, I would like to cut this down to the file name from the entire path name.
This is the path:
\\IP_Address\DirName\DirName\DirName\twProcessDB_20100819173001.trn
Each log file name will be the same length. Just as above. I would like to get the log file name only. I am close. But I cannot seem to get it.
Thanks in advance for your help
Andrew SQLDBA

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

DECLARE @STR VARCHAR(250)

set @STR = '\\IP_Address\DirName\DirName\DirName\twProcessDB_20100819173001.trn'

SELECT RIGHT(@str, CHARINDEX('\', reverse(@str), 1) - 1)

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Perfect

Thank you

I did not think about using 'Reverse' that is what was holding me up, and mine was not working.

Thanks again

Andrew SQLDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Yup, anything you need the last of something that's a really good way to go :hehe:.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

OK, how about the opposite:

I would like to keep the path and do away with the file name:

\\IP_Address\DirName\DirName\DirName\twProcessDB_20100819173001.trn

I would like to have this returned:

\\IP_Address\DirName\DirName\DirName

Thanks

Andrew SQLDBA

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Try this:

DECLARE @STR VARCHAR(250)

set @STR = '\\IP_Address\DirName\DirName\DirName\twProcessDB_20100819173001.trn'

SELECT LEFT(@str, LEN(@str) - CHARINDEX('\', reverse(@str), 1))

This gets everything up to the last \ in the string.

LutzM SSC Guru Points: 107049 More actions · Answer 6

Based on Ninja's_RGR'us code snippet:

SELECT LEFT(@str,LEN(@str)-CHARINDEX('\', REVERSE(@str), 1))

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 7

@derrick: I didn't copy your code! Honestly! ;-):-D

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Derrick Smith SSChampion Points: 10833 More actions · Answer 8

Hah 🙂

Actually ninja's code was very good...I've never really had a case where I needed to use reverse(), but now I bet I can find some. I had always just dealt with fixed length and a combination of left(right()) etc.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 9

Thanks Everyone

That code does the trick

I missed the last numeral 1

That was totally throwing me for a loop

Andrew SQLDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

wow you guys like to work hard!!!

DECLARE @STR VARCHAR(250)

DECLARE @name VARCHAR(250)

set @STR = '\\IP_Address\DirName\DirName\DirName\twProcessDB_20100819173001.trn'

SET @name = RIGHT(@str, CHARINDEX('\', reverse(@str), 1) - 1)

SELECT @name, REPLACE(@str, @name, '')

:w00t::cool::hehe:

LutzM SSC Guru Points: 107049 More actions · Answer 11

Nice one!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Yup... but only use in this case. When manipulating names and adresses you can get in trouble. But in this case the only trouble would be to have the folder name exactly the same as the filename. So for 99.999% of us it shouldn't be an issue :-P.

msnathan SSC Veteran Points: 235 More actions · Answer 13

Hi, This will give you the file name alone

select right(@str,charindex('\',reverse(@str),0) - 1)