Help needed to find string before character when character appears mutliple times

Question

Post reply

Help needed to find string before character when character appears mutliple times

Craig Budd

SSC Veteran

Points: 249
More actions
May 17, 2015 at 6:12 am

#298162

I have a table that contains file paths as
\\Servername\folderA\filenameA
\\Servername\FolderB\FilenameB
and I need a query to return
\\Servername\folderA\
\\Servername\FolderB\
I tried SELECT DISTINCT left(Source, charindex('\', Source)- 0) AS String
FROM Table but that removes everything after the first \ and I need it to return all data before the last \
I am stuck for ideas.
Any help would be gratefully received

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick suggestion, use the charindex and the revers functions.

😎

Craig Budd SSC Veteran Points: 249 More actions · Answer 2

Thanks Eirikur.

I have got it working with the following script.

create table #temp (Value varchar(500), Number int)

insert into #temp

(Value, Number)

select Source, (select charindex('\', reverse(Source))) from Table

select * from #temp

select distinct(left(Value, LEN(Value)- ([Number]-1)))

from #temp

drop table #temp

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

craig.budd (5/17/2015)
Thanks Eirikur.
I have got it working with the following script.
create table #temp (Value varchar(500), Number int)
insert into #temp
(Value, Number)
select Source, (select charindex('\', reverse(Source))) from Table
select * from #temp
select distinct(left(Value, LEN(Value)- ([Number]-1)))
from #temp
drop table #temp

Now, just combine the two formulae so you don't actually need the temp table.

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

As Jeff suggested, it is better to combine the two function calls and skip the temp table. Here are two variations, first one returns both the path and the file name separately and the latter is how it's used as calculated columns.

😎

USE tempdb;

GO

/*

Separate the file name from the full path.

*/

DECLARE @FILE_PATH NVARCHAR(1000) = N'C:\ROOT\FOLDER\SUBFOLDER\THE_FILE.TYPE';

SELECT

SUBSTRING(@FILE_PATH,1, LEN(@FILE_PATH)- CHARINDEX(CHAR(92), REVERSE(@FILE_PATH),1)) AS FOLDER_PATH

,RIGHT(@FILE_PATH,CHARINDEX(CHAR(92), REVERSE(@FILE_PATH),1) - 1) AS THE_FILE_NAME

/* The same logic as a calculated column. */

IF OBJECT_ID(N'dbo.TBL_TEST_PATH') IS NOT NULL DROP TABLE dbo.TBL_TEST_PATH;

CREATE TABLE dbo.TBL_TEST_PATH

(

TP_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_PATH_PT_ID PRIMARY KEY CLUSTERED

,TP_FULL_PATH_FILENAME NVARCHAR(2048) NOT NULL CONSTRAINT CHKCSTR_DBO_TBL_TEST_PATH_TP_FULL_PATH_FILENAME_LEN_EGT_3 CHECK (LEN(TP_FULL_PATH_FILENAME) > 2)

,TP_PATH AS (SUBSTRING(TP_FULL_PATH_FILENAME,1, LEN(TP_FULL_PATH_FILENAME)- CHARINDEX(CHAR(92), REVERSE(TP_FULL_PATH_FILENAME),1))) PERSISTED

,TP_FILE AS (RIGHT(TP_FULL_PATH_FILENAME,CHARINDEX(CHAR(92), REVERSE(TP_FULL_PATH_FILENAME),1) - 1)) PERSISTED

);

INSERT INTO dbo.TBL_TEST_PATH(TP_FULL_PATH_FILENAME)

VALUES (N'C:\ROOT\FOLDER01\SUBFOLDER\THE_FILE01.TLA')

,(N'C:\ROOT\FOLDER01\SUBFOLDER\THE_FILE02.TLA')

,(N'C:\ROOT\FOLDER02\SUBFOLDER\SUBSUBFOLDER\THE_FILE02.TLA')

,(N'C:\ROOT\FOLDER01\SUBFOLDER\THE_FILE02_WITH_A_VERY_LONG_NAME.TLA')

,(N'C:\')

;

SELECT

*

FROM dbo.TBL_TEST_PATH;

Result #1

FOLDER_PATH THE_FILE_NAME

-------------------------- --------------

C:\ROOT\FOLDER\SUBFOLDER THE_FILE.TYPE

Result #2

TP_ID TP_FULL_PATH_FILENAME TP_PATH TP_FILE

------ ----------------------------------------------------------------- ---------------------------------------- -------------------------------------

1 C:\ROOT\FOLDER01\SUBFOLDER\THE_FILE01.TLA C:\ROOT\FOLDER01\SUBFOLDER THE_FILE01.TLA

2 C:\ROOT\FOLDER01\SUBFOLDER\THE_FILE02.TLA C:\ROOT\FOLDER01\SUBFOLDER THE_FILE02.TLA

3 C:\ROOT\FOLDER02\SUBFOLDER\SUBSUBFOLDER\THE_FILE02.TLA C:\ROOT\FOLDER02\SUBFOLDER\SUBSUBFOLDER THE_FILE02.TLA

4 C:\ROOT\FOLDER01\SUBFOLDER\THE_FILE02_WITH_A_VERY_LONG_NAME.TLA C:\ROOT\FOLDER01\SUBFOLDER THE_FILE02_WITH_A_VERY_LONG_NAME.TLA

5 C:\ C:

Craig Budd SSC Veteran Points: 249 More actions · Answer 5

Many Thanks Jeff / Eirikur for your replys.

Eirikur that works perfectly.

Thanks for all your help guys that has been paining me for a while.