SUBSTRING coding problem

Question

Post reply

SUBSTRING coding problem

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
July 16, 2010 at 11:36 am

#224161

Hello Everyone
I am not able to figure this seemingly very small issue out by myself.
I am trying to return the remainder of a string using SUBSTRING method. Nothing more than a complete path along with a file name at the end. All that I would like to have is the complete path, without the file name
example:
J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak
I want only this returned:
J:\SQL 2008 Database Backups\High School Baseball\
But I am not able to figure out using T-SQL how to accomplish this. I am very close, I just cannot get how to find the first '\' when starting at the right.
Thank you in advance
Andrew SQLDBA

Viewing 10 posts - 1 through 9 (of 9 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Try this

DECLARE @strVariable VARCHAR(100)

SET @strVariable = 'J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak'

SELECTSUBSTRING( @strVariable, 1, LEN( @strVariable ) - CHARINDEX( '\', REVERSE( @strVariable ) ) )

Here i have used the REVERSE function to get the length from the right. Didnt test it though as i don't have access to SSMS right now

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/

Ram:) SSCrazy Points: 2246 More actions · Answer 2

Kingston,

It is helpful for me also.. thanks

🙂

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 3

Kingston Dhasian (7/16/2010)
Try this
DECLARE @strVariable VARCHAR(100)
SET @strVariable = 'J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak'
SELECTSUBSTRING( @strVariable, 1, LEN( @strVariable ) - CHARINDEX( '\', REVERSE( @strVariable ) ) )
Here i have used the REVERSE function to get the length from the right. Didnt test it though as i don't have access to SSMS right now

Nicely done, Kingston. A lot of folks end up doing more than 1 Reverse trying to do the same thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Jeff Moden (7/16/2010)
Kingston Dhasian (7/16/2010)
Try this
DECLARE @strVariable VARCHAR(100)
SET @strVariable = 'J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak'
SELECTSUBSTRING( @strVariable, 1, LEN( @strVariable ) - CHARINDEX( '\', REVERSE( @strVariable ) ) )
Here i have used the REVERSE function to get the length from the right. Didnt test it though as i don't have access to SSMS right now
Nicely done, Kingston. A lot of folks end up doing more than 1 Reverse trying to do the same thing.

Thanks for the compliment, Jeff. Just a few days back i had to do a similar job and i wasn't sure if this is a good way to do it. Now i am sure it is the best way. 🙂

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/

Paul White SSC Guru Points: 150467 More actions · Answer 5

A small modification, to allow for input strings that include one or more trailing spaces, and which will also work with a Unicode input string:

DECLARE @strVariable VARCHAR(100) = 'J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak';

SELECT LEFT(@strVariable, 1 + (DATALENGTH(@strVariable)/DATALENGTH(LEFT(@strVariable,1))) - CHARINDEX( '\', REVERSE(@strVariable)));

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 6

Agreed... trailing spaces could come into play (and I always forget about them). Nicely done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

Paul White NZ (7/17/2010)
A small modification, to allow for input strings that include one or more trailing spaces, and which will also work with a Unicode input string:
DECLARE @strVariable VARCHAR(100) = 'J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak';
SELECT LEFT(@strVariable, 1 + (DATALENGTH(@strVariable)/DATALENGTH(LEFT(@strVariable,1))) - CHARINDEX( '\', REVERSE(@strVariable)));

Well, it didn't even cross my mind that trailing spaces will create problems. Thanks for pointing it out, Paul. Now i will be careful with strings and spaces.

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/

Paul White SSC Guru Points: 150467 More actions · Answer 8

All good, Kingston (and Jeff). It was a fairly small refinement to the original excellent answer.

Avoiding the extra REVERSE is neat.

My main reason for posting was to show off the trick to make it work with Unicode and ANSI character sets - the trailing spaces thing was an excuse 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

Joe Celko (7/19/2010)
Watch out for
SET @strVariable = 'J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak\'
The final slash messes up things.

True - though I wonder if that example represents a truly valid path...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi