SELECT colname
FROM tablename
WHERE colname LIKE ('[^A]%')
That will match anything except an 'A'.
Spike
Stephen Milligan
Team Macromedia - ColdFusion
Co-author 'Reality Macromedia ColdFusion MX: Intranets and Content
Management'
http://spikefu.blogspot.com
> -----Original Message-----
> From: Giles Roadnight
> [mailto:[EMAIL PROTECTED]]
> Sent: 21 November 2002 17:17
> To: [EMAIL PROTECTED]
> Subject: [ cf-dev ] Counting titles that do not start with
> letter using SQL
>
>
> I am looping through a query of titles and I want to output
> the results per letter in two columns so under A there would
> be two columns of titles starting with A. To do this I need
> the count of records beginning with A which I have done like this:
>
> <cfquery name="qry_getCurrentLettercount" dbtype="query">
> select count(gameid) as lettercount
> from qry_getgames
> where name like '#request.currentLetter#%'
> </cfquery>
>
> How though do I get the count of titles that do not start
> with a letter?
>
> I could do it like this:
> <cfquery name="qry_getCurrentLettercount" dbtype="query">
> select count(gameid) as lettercount
> from qry_getgames
> where name not like 'A%'
> and name not like 'B%'
> ect...
> </cfquery>
> But I don't want to have to write that out 52 times (upper and
> lowercase)
>
> Also is there a way to make SQL non case sensitive so the
> first query will count upper and lower case?
>
> Thanks
>
> Giles Roadnight
> http://giles.roadnight.name
>
>
>
> --
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED] For human help, e-mail:
> [EMAIL PROTECTED]
>
>
>
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
