String Manipulation Query?-Substring?Easy?

[maverik59]

Hi I am counting the number of repeating recurencies of a field in a mysql database. Each field in the database i require is in the format /orgcharts/images/pic.jpeg
where each pic.jpeg has a different name, how do i search and retrieve JUST the pic.jpeg I have constructed this search but this retireives the whole ie- /orgcharts/images/pic.jpeg

SELECT heading_to,
count(*) as hits
FROM usertrack
WHERE heading_to like "/images/orgcharts/%"
GROUP BY heading_to

I could do it easily in oracle using
select heading_to, substr ( heading_to , instr ( heading_to , '/', -1 , 1 ) +1 , length (heading_to) ) from usertrack

Any Udeas much appreciated
Many Thanks in advance.

 

[BillDHS]

Could you use:
SELECT substring(heading_to from 19 for 50),
count(*) as hits
FROM usertrack
WHERE heading_to like "/images/orgcharts/%"
GROUP BY 1

50 is abritrary, adjust this for field length

 

[maverik59]

many thanks! what i'm using it for doesn't work though the variable i have created....any ideas?
SELECT substring(heading_to,19,length(heading_to)) AS org_name,
count(*) as hits,
org_chart_image,
org_chart_title
FROM usertrack
,org_charts
WHERE heading_to like "/images/orgcharts/%"
AND org_name=org_chart_image
GROUP BY heading_to

 

[r937]

your GROUP BY column list doesn't match the non-aggregate column list in the SELECT

try

GROUP BY
substring(heading_to,19,length(heading_to))
, org_chart_image
, org_chart_title


rudy