select count based on extension

[niall5098]

Hi There,

i have a column that holds file names. i want to query the table and count how many of extension there is, for example:

SELECT count(*)
FROM [dbname].[dbo].[tablename]
where filename like '%.jpg'

Rather than query each extension, is it possible to loop through and give me each extension and a count of that extension?

Thanks in advance.

 

[bborissov]

SELECT REVERSE(LEFT(REVERSE(filename), CHARINDEX('.', REVERSE(filename))-1)) AS Extension,
       COUNT(*) AS Cnt
FROM tablename
GROUP BY REVERSE(LEFT(REVERSE(filename), CHARINDEX('.', REVERSE(filename))-1))

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server

 

[niall5098]

thanks Borisov, getting the following error though

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

 

[simian336]

humm... Borisov's code does not have a substing...

Try this one...

select reverse(substring(reverse(rtrim(yourfield)),1,CHARINDEX('.',reverse(rtrim(yourfield)))-1)),
count(reverse(substring(reverse(rtrim(yourfield)),1,CHARINDEX('.',reverse(rtrim(yourfield)))-1)))
from Yourfile
group by reverse(substring(reverse(rtrim(yourfield)),1,CHARINDEX('.',reverse(rtrim(yourfield)))-1))

Simi

 

[niall5098]

HI Simi,

same error:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

 

[simian336]

do you have some extra code in your ssms window that is also running?

Simi

 

[niall5098]

nope, just this:

select reverse(substring(reverse(rtrim(filename)),1,CHARINDEX('.',reverse(rtrim(filename)))-1)),
count(reverse(substring(reverse(rtrim(filename)),1,CHARINDEX('.',reverse(rtrim(filename)))-1)))
from dbo.activitymimeattachment
group by reverse(substring(reverse(rtrim(filename)),1,CHARINDEX('.',reverse(rtrim(filename)))-1))

 

[simian336]

is "filename" the name of the field you are trying to count?

If not replace it with the actual field name.

Simi

 

[bborissov]

You just don't have [.] in the FileName field.
Did you get any records from this query?

SELECT * FROM tablename WHERE CHARINDEX('.', yourfield) = 0

Borislav Borissov
VFP9 SP2, SQL Server

 

[SQLBill]

Or run this and provide the results so we can see some sample data. We can keep giving you solutions, but if we have to make assumptions on what the data looks like, the solutions may not work.

SELECT TOP 10 filename
FROM tablename

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

Boris is right about the . in the file name.

Let's look at this part of the code.

REVERSE(LEFT(REVERSE(filename), CHARINDEX('.', REVERSE(filename))-1))

For talking purposes, let's remove the REVERSE function.

LEFT(filename, CHARINDEX('.', filename)-1)

Now, suppose there are no dots in the filename. In this situation, the charindex function would return 0.

LEFT(filename, 0-1)

Which is the same as

LEFT(filename, -1)

When I run this on my SQL instance, I get:
[red]Invalid length parameter passed to the left function.[/red]

I know that previous versions of SQL Server mentioned the substring function when passing a negative value to the left (or right) function.

This situation is easy enough to accommodate:

  Select  NullIf(REVERSE(LEFT(REVERSE(FileName), CHARINDEX('.', REVERSE('.' + FileName))-1)), FileName) AS Extension,
          Count(*) As ExtensionCount
  From    YourTable
  Group By  NullIf(REVERSE(LEFT(REVERSE(FileName), CHARINDEX('.', REVERSE('.' + FileName))-1)), FileName)

Notice a couple changes made to Boris's code.

REVERSE('.' + FileName)

I added a '.' to the front of the filename. When this is reversed, the dot will be at the end. The CharIndex function will return the first dot, so having a dot on the end will only make a difference if there is no dot at all in the filename. This means that when there is no dot in the file name, this code will return the entire filename as though it were an extension.

To accommodate this, I used the nullif function, so we basically have this...

NullIf(Big long ugly looking code, filename) As Extension

Basically, if the "big long ugly code" returns the entire string, the nullif function compares it to the original string. If it's the same, NULLIF will return NULL. So... this code will return a filename extension if it exists or NULL otherwise.

Make sense?



-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