Need to query a field and pull out specific information only 1

[ljsmith91]

I need some help, not sure if this is possible.

Is there a way to query a SQL field that has variable length information and pull off only specific needed information. Example:

Field Filename could have any of the following values:

c:\windows\install\bin\thisfile.dat
c:\windows\temp\thisfile.dat
\windows\msu\orange\thisfile.dat
d:\new\hsu\thisfile.dat
e:\old\msu\thatfile.dat
c:\windows\install\bin\thatfile.dat

In this field thisfile.dat is listed 4 times and thatfile.dat is listed 2 times. I need to create a query that will output all of the filename(there are many more) with a file count no matter what folder they exist in. The file name is always at the very end of the field value after the final \.

Is there a way of eliminating the folder names and to get a count in the query output as:

thisfile.dat 4
thatfile.dat 2

Thanks for any help or direction. -ls

 

[gmmastros]

Try this:

Select Right(YourColumnName, CharIndex('\',Reverse('\' + YourColumnName))-1), Count(*)
From   YourTableName
Group By Right(YourColumnName, CharIndex('\',Reverse('\' + YourColumnName))-1)

-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

 

[ljsmith91]

Excellent...thanks so much gmmastros !! That works great !!