October 21, 2009 at 6:54 am
I need to parse certain sections from a sting. The string looks like this:
'\\test20\ECP$\File_Prod\CareCo_82\837\S82960102022005.TXT'
I need to get CareCo_82\S82960102022005.TXT
The section CareCo_82 can be any length as well as S82960102022005.TXT
Is there a way to obtain this info from one select statement?
October 21, 2009 at 7:25 am
It depends. Can you post some more examples? Also, why did you get rid of the \837\? How did you know to do that? Basically it needs to be boiled down to rules that can be true for every instance.
October 21, 2009 at 7:35 am
The file is always in this format with a variable number of characters between the '\'.
The results is a column for a client report and the client doesn't want the section that contains, in this example, '837'
'\\axis552\ABCS$\File_Provs\Anderson_Company\837\X0102022005009253218.TXT'
October 21, 2009 at 7:55 am
October 21, 2009 at 7:56 am
frank what we are looking for is the specific rule so we can help you;
for example is this statment true:
in the data sample like this:
'\\axis552\ABCS$\File_Provs\Anderson_Company\837\X0102022005009253218.TXT'
'\\axis552\ABCS$\File_Provs\Franks_Company\9422\X0102022005009253218.TXT'
'\\axis552\ABCS$\File_ProvsBAK\Lowells_Company\subdir\X0102022005009253218.TXT'
"from right to left,one subdirectory exists before the filename, and that sub directory needs to be removed from the string"
or "from right to left, save two sub directories before the filename, but then remove the second subdirectory"
if you can supply the "rule" like that, we can help you find a way to do it. the length of the strings are variable, fine, but what about the number of subdirectories?
Lowell
October 21, 2009 at 8:09 am
from right to left, save two sub directories before the filename, but then remove the second subdirectory is the correct rule
With the the examples:
'\\axis552\ABCS$\File_Provs\Anderson_Company\837\X0102022005009253218.TXT'
'\\axis552\ABCS$\File_Provs\Franks_Company\9422\X0102022005009253218.TXT'
'\\ABCS$\File_ProvsBAK\Lowells_Company\subdir\Lowells_Company\X0102022005009253218.TXT'
I need
Anderson_Company\X0102022005009253218.TXT
Franks_Company\X0102022005009253218.TXT
Lowells_Company\X0102022005009253218.TXT
October 21, 2009 at 9:19 am
ok this was a kewl project for me;
to solve it, i used a function similar to CHARINDEX, but i used the AT() included below...it returns the Nth occurrance of a specified sting, instead of just the first.
after you have that, it's just fiddling around with the SQL:
results:
Anderson_Company\8X0102022005009253218.TXT
Franks_Company\9X0102022005009253218.TXT
Lowells_Company\LX0102022005009253218.TXT
required function:
CREATE function AT (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000), @nOccurrence smallint = 1 )
returns smallint
as
begin
if @nOccurrence > 0
begin
declare @i smallint, @StartingPosition smallint
select @i = 0, @StartingPosition = -1
while @StartingPosition <> 0 and @nOccurrence > @i
select @i = @i + 1, @StartingPosition = charindex(@cSearchExpression COLLATE Latin1_General_BIN, @cExpressionSearched COLLATE Latin1_General_BIN, @StartingPosition+1 )
end
else
set @StartingPosition = NULL
return @StartingPosition
end
GO
and here's sample data and how i solved it; there is some commented out columns for helping to breakdown the idea
WITH MySampleData As (
SELECT '\\axis552\ABCS$\File_Provs\Anderson_Company\837\X0102022005009253218.TXT' AS FilePath UNION ALL
SELECT'\\axis552\ABCS$\File_Provs\Franks_Company\9422\X0102022005009253218.TXT' AS FilePath UNION ALL
SELECT'\\ABCS$\File_ProvsBAK\Lowells_Company\subdir\Lowells_Company\X0102022005009253218.TXT' AS FilePath )
SELECT REVERSE(FilePath) , --turn it backwards
--dbo.AT('\', REVERSE(FilePath),3), --find the third occurance of the slash
--SUBSTRING(REVERSE(FilePath),1,dbo.AT('\',REVERSE(FilePath),3)), --get the substring of it to the 3rd slash
--SUBSTRING(REVERSE(FilePath),1,dbo.AT('\',REVERSE(FilePath),3)-1),
--use STUFF to repalce anything between slash-one and slash2 with empty string, then reverse it
REVERSE(
STUFF(SUBSTRING(REVERSE(FilePath),1,dbo.AT('\',REVERSE(FilePath),3)-1),
dbo.AT('\',SUBSTRING(REVERSE(FilePath),1,dbo.AT('\',REVERSE(FilePath),3)-1),1)+1,
dbo.AT('\',SUBSTRING(REVERSE(FilePath),1,dbo.AT('\',REVERSE(FilePath),3)-1),2) - (dbo.AT('\',SUBSTRING(REVERSE(FilePath),1,dbo.AT('\',REVERSE(FilePath),3)-1),1) ),
''))
FROM MySampleData
Lowell
October 21, 2009 at 9:26 am
Thanks for your help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply