Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

String Parsing Expand / Collapse
Author
Message
Posted Saturday, September 18, 2010 10:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:13 PM
Points: 9, Visits: 373
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.

Post #988864
Posted Sunday, September 19, 2010 12:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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

Post #988869
Posted Monday, September 20, 2010 6:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 29, 2013 1:44 PM
Points: 144, Visits: 426
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

Post #989325
Posted Monday, September 20, 2010 7:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Post #989371
Posted Monday, September 20, 2010 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 29, 2013 1:44 PM
Points: 144, Visits: 426
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)

Post #989434
Posted Monday, September 20, 2010 9:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 7,191, Visits: 13,645
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
Post #989466
Posted Monday, September 20, 2010 9:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
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.
Post #989479
Posted Monday, September 20, 2010 6:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:13 PM
Points: 9, Visits: 373
Thanks guys! Definately got what I was looking for and more.
Post #989855
Posted Monday, September 20, 2010 8:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Glad we could help!
Post #989876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse