Group all numbers to a single column

[redcoupe]

I am trying to display an alphabetical list menu bar, by getting the first character of a title, then grouping and counting, so the output would be something like:

A (2)
B (32)
C (7)

...etc

My problem occurs when I have a number as the first character of some titles:

1 (2)
4 (12)
A (2)
B (32)
C (7)

...so basically I am trying to group all the numbers together under #, like:

# (14)
A (2)
B (32)
C (7)

Any ideas how this can be done?

Many thanks.

[raf]

try

select count(), if(left(titlevar,1) REGEXP '[[:digit:]]', '#', left(titlevar,1)) as firstletter FROM table GROUP BY firstletter

untested, but something like that should work. I'll try it out this evening if this doasn't work.

The if() will first evaluate if the first character of titlevar (your variable with the title) is a digit. If so, then firstletter will have value #, if not, then firstletter will have the first letter as value

[redcoupe]

Work a treat!

Here is the final code I used:

SELECT if(left(title,1) REGEXP '[[:digit:]]', '0-9', left(title,1)) AS TitleVal, Count(title) FROM testtable GROUP BY TitleVal ORDER BY TitleVal

I had a few problems using #, so I decided to use 0-9.

Thanks for your help. :thumbsup:

[raf]

Glad I could help.

Oh yeah: welcome here !