Parse Records with Comma Delimited Field

[CHAVOUSA]

I created the sub below to obtain the product types of the products table in my shop database and then display it as a drop down box for the web user to select. It all works fine if there is a one phrase only in the product type field. However, I have several records that may have more than one product type in the same field (type) separated by a comma.

The way it builds the drop down box is by reading all records, selecting the distinct value of the 'type' field, sorting them and then building the drop down box with all disctinct values.

How can I check whether my 'type' field has one or more types, then select only the distict values and display them all as a drop down box? hopefully i can just incorporate code into what I already ahve below.

Thanks for all your help.

Sub AddProductTypes
dim name,catSQL, catrs

catSQL="SELECT DISTINCT products.type"
catSQL=catSQL & " FROM products INNER JOIN (categories INNER JOIN prodcategories ON categories.categoryid = prodcategories.intcategoryid) ON products.catalogid = prodcategories.intcatalogid"
catSQL=catSQL & " WHERE (((products.type) Is Not Null) AND ((categories.categoryid)="& cat_id &") AND ((products.hide)=No))"
catSQL=catSQL & " ORDER BY products.type"


set catrs=dbc.execute(catsql)
Response.Write "<select size=""1"" class=""txtfielddropdown"" name=""SelectValue"">"
Response.Write "<option></option>False"
While Not catrs.EOF
name=catrs("type")
Response.Write "<option value=" & name & ">" & name & "</option>"
catrs.MoveNext
Wend
Response.Write "</select>"
catrs.Close
set catrs=nothing

end sub

 

[Sheco]

Maybe you could do it by changing the WHERE clause of your SQL statement to use LIKE

Another way could be to return the same recordset from the database and then use the VBScript Split() function of the field that may have a comma separated list. The Split() function can convert the list into an array and then you can make a For/Next loop to move through the array from bottom to top using LBound() and UBound() to evaluate each item in the list separately.

 

[CHAVOUSA]

The Like statement would only find whether or not the records has the comma. I am not familiar tiwh the Split function.

I am trying to get the code that would actually build the dropdown list based on the value of that 'type' field. Considering that in the filed there may be just one word, or several words separated by a comma.

 

[Sheco]

The following link will help explain the Split function:

http://www.w3schools.com/vbscript/func_split.asp

 

[Tarwn]

The LIKE statement with a few ORs will actually do all the filtering you need. However, as Sheco has pointed out, you will likely need to split the data in your ASP code.

Using the LIKE:
If you have a limited set of possible items you want, simply do something like:

 WHERE (((products.type) Is Not Null) AND ((categories.categoryid)="& cat_id &") AND ((products.hide)=No))
 AND (products.type LIKE ',abc,' OR products.type LIKE ',def,' OR ...)

As far as this whole thing is concerned, though, you really out to redesign this if you have the ability. Stuffing multiple comma-delimited values into a single record is silly. The whole point of a relational database is to have multiple types of information related in a meaningful way. Instead you have values that should be in a related table all shoved into a single field. This is not only inefficient but will cause workarounds in your SQL statements and code in order to continue building off of (as your no doubt noticing by now).

Your best bet (if you can and at a minimum) is to split the product types into their own table and store a product id and the type. Your SQL statement will get a little more complicated but it won't be dynamic anymore, saving you a great deal of work. If this is SQL server then that means you can drop the whole thing into a Stored Procedure and gain a good amount of efficiency on your searches. You also get neat things like the ability to output a list of all the product types in the database in a single, simple query. Additionally you will not need to do extra splitting and such in your code.

-T

Best MS KB Ever:

http://support.microsoft.com/kb/555375