If you're using MSSQL, you can use the following syntax:
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]
