Is there a "like" option?

crabjoe · June 6th, 2008, 03:17 PM

In SQL you can do

Code:

where LAST_NAME like 'JOH%'

Is it possible to do something similar in ASP in an IF?

Code:

If LAST_NAME like "JOH%" Then

Thanks!

dparsons · June 6th, 2008, 03:57 PM

It all depends on what exactly you are going for. If you are looking for the exsistance of a given group of characters in a string such as you have shown, you can use the InStr() function.

Consider:

Dim string1, pos

string1 = "Jonathan"
pos = InStr(string1, "Jon")
Response.Write pos '1 would be wrote to the screen

hth.
-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Old Pedant · June 6th, 2008, 04:05 PM

Code:

Sure... The INSTR() function!!!

WHERE field LIKE '%xyz%' ==>>
      IF InStr( 1, field, "xyz", vbTextCompare ) > 0 Then 

WHERE field LIKE 'xyz%' ==>>
      IF InStr( 1, field, "xyz", vbTextCompare ) = 1 Then 

WHERE field LIKE '%xyz' ==>>
      IF InStr( 1, field, "xyz", vbTextCompare ) = Len(field)-Len("xyz")+1 Then 

The vbTextCompare emulates SQL by making the search non-case-sensitive.
Use vbBinaryCompare (or just omit the parameter) if you want a case-sensitive compare.

As alternatives to the latter two searches, you could do:

WHERE field LIKE 'xyz%' ==>>
      IF StrComp( Left(field,LEN("xyz")), "xyz", vbTextCompare ) = 0 Then 

WHERE field LIKE '%xyz' ==>>
      IF StrComp( Right(field,LEN("xyz")), "xyz", vbTextCompare ) = 0 Then 

You decide which you prefer.

http://msdn.microsoft.com/en-us/library/wybb344c(VS.85).aspx

Old Pedant · June 6th, 2008, 04:07 PM

A comment on DPARSONS' answer:

Don't forget that INSTR( ) used with only two parameters is case sensitive. So it doesn't really match SQL's "LIKE" operator in that form. The alternative form I show allows you your choice of case sensitivity or not.

crabjoe · June 9th, 2008, 03:42 PM

Thanks Folks!!