SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String Parsing


String Parsing

Author
Message
dave.molyneaux
dave.molyneaux
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 421
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.
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
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


Keith Langmead
Keith Langmead
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 428
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


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16416 Visits: 19554
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Keith Langmead
Keith Langmead
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 428
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)


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16416 Visits: 19554
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
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.
dave.molyneaux
dave.molyneaux
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 421
Thanks guys! Definately got what I was looking for and more.
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 5549
Glad we could help! :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search