Avatar of HairJam

asked on 

Classic ASP array and SQL SELECT question

Hi there,

I have a record in my database which is comma delimeted, an example might be "orange, apple, pear" which is the result of a user selecting three items from a multi-select box or three checkboxes of the same name but with different values.

I have a form at the front-end containing a <select multiple> box containing a list of different fruits (as an example) which the User can select multiple items from to search. So when a user submits the form the Response.Form("fruit_type") value might be "orange, pear, strawberry, lemon" (if they picked 4 items from the select box list.

I'm trying to then construct a SQL SELECT statement to interogate my database which would have the following structure:

strSQL = "SELECT * FROM tblFruit WHERE fruit_type LIKE '%orange%' AND fruit_type LIKE '%pear%' AND fruit_type LIKE '%strawberry%' AND fruit_type LIKE '%lemon%' ORDER BY id"

The idea being that my record in the database which contains "orange, apple, pear" will be picked up because it contains one or more matching results.

Fundamentally my thought process would suggest I needed to put the form value "orange, pear, strawberry, lemon" into an array which is split at the comma (,) and then construct my SQL SELECT statement using a for...next loop.

I can't seem to figure out, however, how to actually code this up.

Can anyone help?

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

that should be OR and not AND...

strList = "orange,pear,strawberry,lemon"
arrList = split(strList, ",") 
strSQL = "SELECT * FROM tblFruit WHERE ',' + fruit_type ',' LIKE '%," & join(arrList, ",%' OR ',' + fruit_type ',' LIKE '%," ) & ",%' ORDER BY id"

Open in new window

Avatar of HairJam


Hmm - I'm getting an error here:

Line 1: Incorrect syntax near ','.

Here's the code I'm using:

Front-end select box...
<select name="technologies" multiple>
<option value="all" selected>All</option>
<option value=".Net">.Net</option>
<option value="Active Directory">Active Directory</option>
<option value="BizTalk">BizTalk</option>
<option value="CRM">CRM</option>
<option value="Live Communications Server">Live Communications Server</option>
<option value="Office">Office</option>

strFormTechnology = Request.Form("technologies")
ListArray = Split(strFormTechnology,",")

strSQL = "SELECT * FROM tblTechnology WHERE ',' + ma_technology ',' LIKE '%," & join(ListArray, ",%' OR ',' + ma_technology ',' LIKE '%," ) & ",%' ORDER BY id"
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of HairJam


I'm using SQL Server 2000 for this particular project. :-)
Avatar of HairJam


Many thanks for your help. It is working very well now. If you think using & instead of + would be better in the case of SQL Server 2000 just let me know, but certainly it is working perfectly at the moment as is. Thanks again!
& would have been for Ms Access :)
+ is just fine for ms sql server.

glad I could help

Active Server Pages (ASP) is Microsoft’s first server-side engine for dynamic web pages. ASP’s support of the Component Object Model (COM) enables it to access and use compiled libraries such as DLLs. It has been superseded by ASP.NET, but will be supported by Internet Information Services (IIS) through at least 2022.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo