String manipulation in SQL string ? My query is wrong ?

[Wesam]

Hi,

I have question: how can I in (SQL string) ask DAO to search for records that contain specific data in the value of these records.

I.e.: I have table named "Table1", and it contains one field named "Group_Index" (of type string). This field contains data in the form "x,x,x,x,x,x,x,x" .. where x is a number (of course a string number) for example "2,34,5,6,7,8". I need a query (SQL query) that searches this field for a number for example "2" and returns me this record. The problem is because the field type is string the search is incorrect; consider the following:

I have these records:

"2,3,4,1,6,7"
"22,34,3,5,6,7"
"12,3,7,6,9,0"

if I search the value "2" I would use a query like:

SqlCommand="SELECT ALL FROM [Table1] WHERE [Group_Index] LIKE '*2*'"

unfortunately this query will return me all above mentioned fields because each of them contain "2" (the string). How can strict SqlCommand to search only "2" ???

I appreciate your help in advance,
Thank you

Wesam.

------------------

[Jamppa]

You can use commas also in query.

SqlCommand="SELECT ALL FROM [Table1] WHERE [Group_Index] LIKE '2,*' or [Group_Index] LIKE '*,2,*' or [Group_Index] LIKE '*,2'"

[Wesam]

I did this as well and I faced the same problem in different cases, for example if I have the following cases:

"2,45,6,6,89" .. this case I neither use ",2" nor ",2," .. only "2,"

"3,5,2,5" .. this case I can use "2," ",2," and ",2,"

"3,5,3,5,2" .. this case I neither use ",2," nor "2," .. only ",2"

so how can I write a smart SQL command that understands these situations together ??

Thank you and awaiting your help..

Wesam.


------------------

[JHausmann]

The issue I have with using "like", in the form you guys have been talking about, is that it will use a table scan for *each* condition . It's Ok if the table is small but will become a bottleneck with significant records.

The approach I would use, if there's a unique key attached to the table is as follows (and this is only if I had to use a SQL statement, for a bound control for example. For something like a control box, I would populate directly) :

1) Query the entire table returning the "number" string and unique key for each record
2) Parse the "number" string looking for whatever value you're searching for. The following works if there's no quote chars (I use a routine ahead of this to replace them with the space char) in the string.

Do Until nFieldNum > MAXFIELDS
sParser1 = LTrim(sParser2) 'remove any leading spaces
If Mid(sParser1, 1, 1) = sQuoteDelim Then
'first character, now, is "
sParser2 = Mid(sParser1, 2, Len(sParser1))
'eliminate first quote
nTestPos1 = InStr(2, sParser1, sQuoteDelim, 1)
'locate matching quote
sValue = Mid(sParser2, 1, nTestPos1 - 1)
sParser1 = Mid(sParser2, nTestPos1, Len(sParser2))
'get rid of the data between quotes and quotes
sParser2 = LTrim(sParser1)
sParser1 = sParser2
'again, remove leading spaces (if any)
If Mid(sParser2, 1, 1) <> COMMA Then 'unmatched quotes
Err.Number = 1
End If
End If
'skip to comma, there are no quotes in the way
nTestPos2 = InStr(1, sParser1, COMMA, 1)
If nTestPos2 <> 1 Then 'we need to get numeric value
sValue = Mid(sParser1, 1, nTestPos2 - 1)
End If
sParser2 = Mid(sParser1, nTestPos2 + 1, Len(sParser1))

nTest= CINT(Mid(sValue, 1, Len(sValue) - 1))

if nTest= checkvalue then 'what you're searching for was found
nFieldNum=MAXFIELDS + 1
'you want to save the unique key(s) into one or more strings at this point

uniquekey1 = uniquekey1 & "," & retrievekey
else
nFieldNum = nFieldNum + 1
end if
Loop

3) at the end of the table build your SQL statement using uniquekey:

"SELECT ALL FROM [Table1] WHERE uniquekey in(" & uniquekey1 & ")"


[This message has been edited by JHausmann (edited 08-09-1999).]

[Wesam]

Thank you for your post, but as I understood you read the entire Table according to a specific unique key (so to have distinct records from each other) and then to process each record separately (search it for the value) and then re-query the table according to the new CONDITIONS, however, (if this is what you meant) I think it's even slower than other method, because QUERY+STRING MANIPULATION+USAGE OF VB FUNCTIONS (LIKE MID, ...ETC) slows down the whole process.
Please if I'm wrong then reply again illustrating your point of view, anyway I got an excellent idea from the code you provided, that is the usage of delimiters. If I use quotes as delimiters then I'll just need to say "..[Group_Index] LIKE '"1"'" instead of a comma.
I grateful for you assistance, and awaiting for your reply (if any).

Wesam.

------------------

[JHausmann]

You would think my routine would be slower but, except for small tables (small record counts where getting the whole table doesn't take much longer than getting a single record) , it's not. The reason is that a table scan serially reads thru the table collecting data. In one of the examples, three scans would've been used. That would take longer than my example of one scan and one indexed read, with VB string manipulation in between.

Some of the tables I deal with have millions (not a typo) of records. If I use table scans on them, it will take a very long time to run or timeout before finishing.