September 18, 2010 at 10:17 pm
I have a string similar to C:\Documents and Settings\file.xls
I need to write a query to find the second occurrence of \ and replace everything after it with blank space so that I'm only left with C:\Documents and Settings\
Thanks for any help.
September 19, 2010 at 12:41 am
How about this:
--: Declare local variables
DECLARE @command VARCHAR(50)
DECLARE @Folder VARCHAR(50)
SET @Folder = 'P:\Documentary'
SET @command = 'dir /b /s "'+@Folder+'"' -- Place you
-- Declare a temp table to hold the file names inside a folder
DECLARE @FolderAndFiles TABLE
(
Sl_Number INT IDENTITY(1,1) ,
FileNameWithFolder VARCHAR(1024)
)
-- Insert data into the table
INSERT INTO @FolderAndFiles
EXEC master..xp_cmdshell @command
-- Sample Select
; WITH CTE AS
(
SELECT REVERSE(FileNameWithFolder) Reverse_File_Name
FROM @FolderAndFiles
WHERE FileNameWithFolder IS NOT NULL
),
Char_Positions AS
(
SELECT
Reverse_File_Name ,
CHARINDEX('\',Reverse_File_Name) Last_Dash_Pos , -- Find the first index of a forward slash
CHARINDEX('.',Reverse_File_Name) Dot_Pos -- Find the first index of a dot
FROM CTE
)
SELECT
FolderName = REVERSE( STUFF(Reverse_File_Name,1,Last_Dash_Pos,'') ) ,
FileNameWithExtension = REVERSE( LEFT(Reverse_File_Name, (Last_Dash_Pos-1) ) ) ,
FileNameWithoutExtension = REVERSE( SUBSTRING(Reverse_File_Name, ( Dot_Pos + 1 ),( Last_Dash_Pos - Dot_Pos -1 ) ))
FROM Char_Positions
September 20, 2010 at 6:58 am
Think this might be more what you're looking for :
declare @origstring varchar(50)
declare @strlength int
declare @teststr varchar(5)
declare @newstring varchar (50)
set @origstring = 'C:\Documents and Settings\file.xls'
set @strlength = len(@origstring)
set @teststr = ' '
while @teststr != '\'
begin
set @teststr = substring(@origstring,@strlength,1)
if @teststr != '\' set @strlength = @strlength -1
end
set @newstring = substring(@origstring,0,@strlength + 1)
select @newstring
September 20, 2010 at 7:47 am
Nicking ColdCoffee's setup:
-- Declare a temp table to hold the file names inside a folder
DECLARE @FolderAndFiles TABLE
(
Sl_Number INT IDENTITY(1,1) ,
FileNameWithFolder VARCHAR(1024)
)
-- Insert data into the table
INSERT INTO @FolderAndFiles (FileNameWithFolder)
SELECT 'C:\Development\Source_Files\VFP9Apps\Trunk' UNION ALL
SELECT 'C:\My Documents\VFPDevelopment' UNION ALL
SELECT 'C:\Program Files\Microsoft SQL Server' UNION ALL
SELECT 'C:\Program Files\Microsoft SQL Server Compact Edition\v3.5'
SELECT Sl_Number, FileNameWithFolder, LEFT(FileNameWithFolder, n)
FROM (
SELECT Sl_Number, FileNameWithFolder, n.n,
nthPos = ROW_NUMBER() OVER(PARTITION BY Sl_Number ORDER BY n.n)
FROM @FolderAndFiles
INNER JOIN (SELECT n = ROW_NUMBER() OVER (ORDER BY NAME) FROM dbo.SYSCOLUMNS) n
ON SUBSTRING(FileNameWithFolder, n.n, 1) = '\'
) d WHERE nthPos = 2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 20, 2010 at 8:36 am
Perhaps I'm missing something here, but no where in Dave's original query did he mention anything about parsing folder structures, parsing multiple strings or anything like that. He simply asked how to take a single string which he already has (presumably from some other code he already knows how to write) and pull out a particular substring.
FYI Dave, just in case you wanted to, using the code I gave you above, if you did happen to want to pull out the filename from the string as well you can do so by adding :
declare @filestring varchar 50
set @filestring = substring(@origstring,@strlength +1,len(@origstring)-@strlength)
September 20, 2010 at 9:07 am
keith-710920 (9/20/2010)
Perhaps I'm missing something here, but no where in Dave's original query did he mention anything about parsing folder structures, parsing multiple strings or anything like that. He simply asked how to take a single string which he already has (presumably from some other code he already knows how to write) and pull out a particular substring.
You are absolutely right Keith, at least about the latter part. ColdCoffee & I posted our column-centric solutions because - well, because that's what is usually asked for. Both solutions will work with a string variable with a little simple modification.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 20, 2010 at 9:16 am
Chris Morris-439714 (9/20/2010)
keith-710920 (9/20/2010)
Perhaps I'm missing something here, but no where in Dave's original query did he mention anything about parsing folder structures, parsing multiple strings or anything like that. He simply asked how to take a single string which he already has (presumably from some other code he already knows how to write) and pull out a particular substring.You are absolutely right Keith, at least about the latter part. ColdCoffee & I posted our column-centric solutions because - well, because that's what is usually asked for. Both solutions will work with a string variable with a little simple modification.
I absolutely agree with u Morris, the solution i prepared (and the one u prepared) are column-centric ; a lot of folks, for brevity sake, show only a row of data they actually have. BUt it is always better to provide a column-centric solution.
@keith, as Morris said, its just a matter of replacing the column name with local variable name, should it be a request to parse local variable.
September 20, 2010 at 6:01 pm
Thanks guys! Definately got what I was looking for and more.
September 20, 2010 at 8:53 pm
Glad we could help! 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply