Show data at any giving point of the filed 3

[ciarra41]

I have a list of file names in a table; I'm only trying to query the report name starting at the last forward(\) slash. So anything starting at the last slash will be the report name.


k:\documents\myfolder1\reports\analysis\march\04\ordersReports.xls

It should look like this. Any example sql to parse this and/or use a substring to show what's left after that last slash.

ordersReports.xls


SELECT filename, RIGHT(reportname, len(reportname) -50)
FROM tblReportsProcess group by reportname


This worked partially but I discover not all the reports names ends with the same length.

 

[SgtJarrow]

There might be some simplier methods to parse out a directory listing in SQL but the one I use is this:

I left all the step processes in to show how I got there. The last column in the select is the template you want to work with.

DECLARE @SomeString VARCHAR(5000)
SET @SomeString = 'k:\documents\myfolder1\reports\analysis\march\04\ordersReports.xls'

SELECT
	@SomeString 'Original',
	REVERSE(@SomeString) 'Reversed',
	CHARINDEX('\', REVERSE(@SomeString)) 'IndexOfReversedSlash',
	SUBSTRING(REVERSE(@SomeString),0,  CHARINDEX('\', REVERSE(@SomeString))) 'SubstringOfIndexOfReversedSlash',
	REVERSE(SUBSTRING(REVERSE(@SomeString),0,  CHARINDEX('\', REVERSE(@SomeString)))) 'Re-Reversed'

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

Try this:

[tt]Select Right(FileName, CharIndex('\',Reverse('\' + FileName))-1)[/tt]

If this works for you and you would like me to explain it, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

mstrmage1768,

Your suggestion doesn't work if the '\' is not found.

For example, try this:
[tt]
SET @SomeString = 'mstrmage1768.xls'
[/tt]

Notice in my code, this part: Reverse('\' + FileName)

I force there to be a '\' character in the search string so that, no matter what, there will always be a value.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

Why I said there are probably better methods. Thank you for showing me a method to add to my code base. I can always count on your knowledge.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[ciarra41]

Thanks guys!