SUBSTRING in query: proper use

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SUBSTRING in query: proper use

Author

Topic

contiw
Starting Member

4 Posts

Posted - 2006-10-11 : 15:37:57

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
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-11 : 16:08:58

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>

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-11 : 16:37:47

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

contiw
Starting Member

4 Posts

Posted - 2006-10-11 : 18:54:52

Thanks everybody: both mid() and left() work ok.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources