Using LIKE to search a Database 2

[Ianpen]

Hi,

Firstly, I have to confess that I am a newby to ASP.

I have a Form setup that retrieves the variables entered by users to display a policy number. The problem lies in the way the policies have been entered originally (about 500,000 records). There could be variations in the style that people will know them by. eg. One User may call a policy 04/1234 and another user may call it 04.1234 so I want to be able to search for just 1234 if needed. I realise that I need to use LIKE and need to use wildcards, the only problem is that I don't know where to place the wildcards. I've tried a few combinations to no avail.

set objRS = con.execute("SELECT * FROM web WHERE policy = '"+ request("policy") +"' ORDER BY policy ASC")

If anyone could help I would be very gratefull.

 

[DotNetGnat]

Try this:

set objRS = con.execute("SELECT * FROM web WHERE policy LIKE '%" request("policy") "%' ORDER BY policy ASC")

-DNG

 

[a6m1n0]

set objRS = con.execute("SELECT * FROM web WHERE policy = '"+ request("policy") +"' LIKE '04%1234' ORDER BY policy ASC")

Just as an example. '%' is the wildcard character to use with LIKE.

 

[FesterSXS]

try...

"SELECT * FROM web WHERE policy CONTAINS '" & request("policy") & "' ORDER BY policy ASC;"

Also - instead of using * in your SQL statement, just name the field names that you require in your recordset - it is more efficient that way.

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[Ianpen]

Neither of these seem to Work. DotNetGnat your page couldn't be displayed (could this be something I'm not doing right on my side). And A6m1nO yours couldn't find an expample. Although I think DotNetGnat seems to be the closest as the WildCards need to be relating to the info posted from the form. I might be wrong but thats why I am asking you guys.

Thanks for you help so far guys, but Anymore ideas?

 

[Ianpen]

Hi Tony

Yours gave a syntax error (missing operator)....

Any ideas?

Thanks
Ian

 

[Creto]

Would there always be a form of separator, like the "/" or the "." you used in your example? Perhaps a list of them, or is it just a matter of completely random information without any kind of naming convention?

 

[Ianpen]

Creto, This is totally random. That is the problem that I have. This is a database that we have got from one of our customers, which includes variables from their customers. In access I would have got round it like this

Like "*" & [Enter Policy] & "*"

Hope that explains a bit more for you!

 

[steven290]

this is what you need

strsql="SELECT * FROM web WHERE policy like '%"& request("policy") &"' ORDER BY policy ASC;"

this will find where the values ends with 1234

 

[Ianpen]

Steve290, cheers mate. That the one. I have modified it a bit, but this now will search like starting with ending with or containing.

set objRS = con.execute ("SELECT * FROM web WHERE policy like '%"& request("policy") &"%' ORDER BY policy ASC;")

Thanks alot for all of your help, this site deserves an OBE.

 

[DotNetGnat]

thats what i suggested in the beginning i guess...

-DNG