We help IT Professionals succeed at work.

SQL query for first letter in field

I am doing a coldfusion SQL query and want to determine if the first letter of a title is A-F, G-M, N-R, S-Z. My query below gives me an error. Also I should make it into Uppercase before I compare but haven't tried that yet either. What would be the best way to do this? Thanks.

<cfquery name="getAF" dbtype="query">
    select ID, Title
    from getall
    where left(Title,1) like ([A-F])
    order by Title
 </cfquery>
Comment
Watch Question

CERTIFIED EXPERT

Commented:
where left(Title,1) in ('A','B','C','D','E','F')
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
    > Also I should make it into Uppercase before I compare

Do you mean you only want titles that start with uppercase "A", not lower case "a"?

Author

Commented:
Yes, I want to only get titles that start with uppercase A- uppercase G etc.
upper() make it into Uppercase before  compare .

select ID, Title, 
        (case 
        when left(upper(Title),1) in ('A','B','C','D','E','F') then '[A-F]'
        when left(upper(Title),1) in ('G','H','I','J','K','L','M') then '[G-M]'
        when left(upper(Title),1) in ('N','O','P','Q','R') then '[N-R]'
        when left(upper(Title),1) in ('S','T','U','V','W','X','Y','Z') then '[S-Z]'
	    end) as InGroup
    from getall
    order by Title

Open in new window


best regards.
111.jpg
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
For case sensitive comparisons, try one of these methods. For example, you could adapt knightEknight's example like this:

   WHERE  LEFT(Title,1) COLLATE SQL_Latin1_General_CP1_CS_AS IN ('A','B','C','D','E','F')

If the list of letters can vary, be sure to use cfqueryparam

   WHERE  LEFT(Title,1) COLLATE SQL_Latin1_General_CP1_CS_AS IN (
          <cfqueryparam list="true" csqltype="cf_sql_varchar" value="#yourList#">
        )
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
      > upper() make it into Uppercase before  compare

That doesn't make the comparison case sensitive.

Author

Commented:
So I have this but I am still getting the error message below.
 <cfquery name="getAF" dbtype="query">
            select ID, Title,BookTitleDisplay
            from getall
            WHERE  LEFT(Title,1) COLLATE SQL_Latin1_General_CP1_CS_AS IN ('A','B','C','D','E','F')
            order by Title
                 </cfquery>


Query Of Queries syntax error.
Encountered "LEFT. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,
 
The error occurred in \\si-wcm05\IWServer\default\main\public\saam\saam-mainsite\WORKAREA\navarts_final\visit\stores\online\books\title\index.cfm: line 105

103 :                   <div class="shop_by">
104 :            
105 :             <cfquery name="getAF" dbtype="query">
106 :             select ID, Title,BookTitleDisplay
107 :             from getall
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> <cfquery name="getAF" dbtype="query">

I didn't know it was a QoQ.  QoQ are VERY basic. They don't support db specific syntax (like what I posted).  They have no support for functions like LEFT() either.

You'll either have to do the comparison in your database query OR loop through the list of letters and construct a LIKE condition for each one. Which option would you prefer?

Author

Commented:
I'll try the database query. I didn't realize QofQ wouldn't support this.
CERTIFIED EXPERT

Commented:
           select ID, Title,BookTitleDisplay
            from getall
            WHERE  ASCII(LEFT(Title,1))  BETWEEN 65 AND 70
            order by Title

This is going to be slow, because it will do a table scan (as will most any other option).  To speed things up, create a single indexed persisted calculated field with the first char of the title.
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
Yeah, QoQ are run completely memory (not against your database).  So they support very few functions: CAST, UPPER, LOWER  and maybe one or two others.  But that's it.

Author

Commented:
When I changed my query to the database rather than QoQ, it worked.
Many thanks for the explanation!
if you want case sensitive ,use  'CS',  not 'CI'.
for example: SQL_Latin1_General_Cp437_CS_AS_KI_WI

alter table getall
alter column Title char(3) COLLATE SQL_Latin1_General_Cp437_CS_AS_KI_WI

not use SQL_Latin1_General_Cp437_CI_AS_KI_WI.

alter table getall
alter column Title char(3) COLLATE SQL_Latin1_General_Cp437_CI_AS_KI_WI

what's those modes in your database you can select this :
select * from ::fn_helpcollations()


best regards

Explore More ContentExplore courses, solutions, and other research materials related to this topic.