regular expression sql question
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=7408
Printed Date: 01 April 2026 at 1:00am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: regular expression sql question
Posted By: ngaisteve1
Subject: regular expression sql question
Date Posted: 19 November 2003 at 9:15pm
|
My system is about directory I managed to query according to which 2 alphabetic user select. sql = "Select * FROM customer WHERE companyname LIKE '" & strAlpha1 & "%' " sql = sql & "OR companyname LIKE '" & strAlpha2 & "%' " sql = sql & "ORDER BY companyname" But the problem I face is those companys which are not start with alphabetic like 333 Sdn Bhd, _3COM Limited or etc. I know I can use NOT LIKE but anyway other way besides that because if I use NOT LIKE one by one like: sql = "Select * FROM customer WHERE companyname NOT LIKE 'A%' " sql = sql & "OR companyname NOT LIKE 'B%' " sql = sql & "ORDER BY companyname" Then I need to repeat it for 26 times since there are 26 alphabetic. Thanks. |
Replies:
Posted By: ljamal
Date Posted: 19 November 2003 at 9:43pm
|
You could use the ASCII function with the lowercase function like this
select * from customer where ASCII(LCASE(left(companyname,1))) not between 97 and 122 order by companyname This will return everything that begins with a non-alpha character as the ASCII number for all the alphabetic lower case characters lies between 97 (a) and 122 (z), inclusive. ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
Posted By: ljamal
Date Posted: 19 November 2003 at 9:44pm
|
That assumes you use SQL Server, I'm not sure about Access as I rarely use it. ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
Posted By: ngaisteve1
Date Posted: 19 November 2003 at 11:13pm
|
I tried and it says there Microsoft JET Database Engine (0x80040E14) |
Posted By: ljamal
Date Posted: 20 November 2003 at 1:23am
As I said above ... Your error means that the ASCII function is not supported in Access. But since you didn't look it up I guess, I will for you.... The ASCII function in Access is ASC, if you replace ASCII with ASC in the SQL statement above it should work for you. I even tested it with the ASC function in Access, so it does work. In the future if you are not willing to do the extra legwork when someone points you in the right direction, just hire someone to do it for you. ------------- L. Jamal Walton http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming |
ljamal wrote:Posted By: ngaisteve1
Date Posted: 20 November 2003 at 2:22am
|
I'm sorry. I will put extra effort in the future. Thanks a lot. It works. |