SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 SUBSTRING in query: proper use		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

contiw
Starting Member

4 Posts
Posted - 10/11/2006 :  15:37:57  Show Profile  Reply with Quote
This is a query-of-query in ColdFusion and MSAccess

This works ok

<cfquery name="session.getAlpha" dbtype="query">
select distinct signerName
from session.getSigs
</cfquery>

Instead, I would like to select just the "first letter" of signerName in
alphabetical order:

<cfquery name="session.getAlpha" dbtype="query">
select distinct substring(signerName,1,1) as alpha
from session.getSigs
orderby alpha
</cfquery>

and it fails. This is giving me a rough time, im just not making the query
right ...
Any help much appreciated!

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts
Posted - 10/11/2006 :  16:08:58  Show Profile  Reply with Quote
Try

<cfquery name="session.getAlpha" dbtype="query">
select distinct substring(signerName,1,1) as alpha
from session.getSigs
order by substring(signerName,1,1)
</cfquery>

or
<cfquery name="session.getAlpha" dbtype="query">
select distinct substring(signerName,1,1) as alpha
from session.getSigs
order by 1
</cfquery>
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 10/11/2006 :  16:37:47  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
or, since this is Access:

select distinct mid(signerName,1,1) as alpha
from session.getSigs
order by mid(signerName,1,1)

or better yet:

select distinct left(signerName,1) as alpha
from session.getSigs
order by left(signerName,1)

- Jeff
Go to Top of Page

contiw
Starting Member

4 Posts
Posted - 10/11/2006 :  18:54:52  Show Profile  Reply with Quote
Thanks everybody: both mid() and left() work ok.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000