Convert search string to SQL query

Hi Experts

I'd like to create a query out of a comma delimited "search string"
egSearchstring =  "Item1, Item2, Item3........."
to
sql = sql & "where [Data] like '*Item1*' or [Data] like '*Item2*' or [Data] like '*Item3*' "

I'm thinking of converting "searchstring"  into a "sql string" and then writing
sql = sql & sqlstring

Your ideas on creating that "sql string" would be much appreciated. ( Working in VB6)

Many thanks

Peter

peterdarazsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
a small correction
egSearchstring =  "Item1,Item2,Item3........." 
sql = sql & "where [Data] like '*" & join( split(egsearchString, ","), "*' or [Data] like '*" ) & "*' "

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
here we go:
egSearchstring =  "Item1,Item2,Item3........." 
sql = sql & "where [Data] like '*" & join( split(egsearchString, ","), *' or [Data] like '*" ) & "*' "

Open in new window

0
 
peterdarazsAuthor Commented:
Thanks Angellll

This looks like the solution. I'm a little confused as to the second 'or [Data]' in the routine , but assume this will join to each split item to anothe 'or [Data]' as if it were hard coded. If this is the case , then 100% and complete solution . many thanks

Please let me know if my assumption was incorrect though....

Thanks again

Peter
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
<Grading Comments>
Thanks Angellll

This looks like the solution. I'm a little confused as to the second 'or [Data]' in the routine , but assume this will join to each split item to anothe 'or [Data]' as if it were hard coded. If this is the case , then 100% and complete solution . many thanks

Please let me know if my assumption was incorrect though....

Thanks again

Peter
</Grading Comments>

the first
sql = sql & "where [Data] like '*"
is for the first search item

the join is for what comes between the search items:
join(xxxx , "*' or [Data] like '*" )

and finally:
& "*' "

is to close the last item (which would be the first item if there was only one item)
0
 
peterdarazsAuthor Commented:
Ok , that's perfect then, and apprecaite the explanation.

Enjoy your day.
Peter

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.