By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Remove numbers from field in ORDER BY

Expert 100+
P: 303
I have a table (books) with 2 columns (book_id, book).
The table looks like:
[HTML]19 Psalms
20 Proverbs
60 1 Peter
61 2 Peter[/HTML]
I want to sort this by text first, then number if a book has a number. So the sorted data would look like:
[HTML]60 1 Peter
61 2 Peter
20 Proverbs
19 Psalms[/HTML]
My query is
Expand|Select|Wrap|Line Numbers
  1. SELECT book
  2. FROM books
  3. ORDER BY trim(replace(replace(replace(book,'3',''),'2',''),'1','')), book
This works, but isn't there a better way?
Oct 2 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 303
My problem with this query is that replace is a VBA function, so it doesn't work when I try the query outside Access. Is there another way to do this?
Oct 2 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
"My problem with this query is that replace is a VBA function, so it doesn't work when I try the query outside Access. Is there another way to do this??"

Where exactly are you trying the query "outside of Access?"

Linq ;0)>
Oct 2 '08 #3

Expert 100+
P: 303
Sorry I didn't specify. I'm trying to use VBScript. Even within Access, I would like to find any better way to sort the data.
Oct 2 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.