how can I search a table in database?

Question

I have a column named "address". worker's addresses is written in here. i want to search for address column but with "WHOLE WORD" technique.

for example, suppose that, there is the address:
"abc street, def road no:21"

when i use that command:
select * from Table1 where address LIKE 'ab'

it gives the above address, because of abc street.

but i don't want to get that result, because there is no "ab" in that address. in other words, i want to search a "whole word" in the address.

in other words, to get this result, the user must type "abc" not "ab".

what command should i use for that? i am a newbie in asp. if you can help me it will be gladly appreciated.

Answer 1

Hi,
Instead of using LIKE operator, u should use instr() function.
Logic is -> u want to search whole words only not part of any word, it implies that the word will be having a space before and after. i.e. "blankspaceABCblankspace". or in other words ur word would be preceeded and followed by a space. So instead of using "ABC" only u should use " ABC " i.e. with spaces.
Now use this condition-> WHERE instr(1,address," " + "abc" + " ")>0
If any abc is found in ur address column, instr() will return value >0 and that record will be listed.

Answer 2

When using like, you want to use % to indicate areas where you don't care what is there. Instead of using 'ab' you want to use '%ab%'.