Link to home
Start Free TrialLog in
Avatar of Richiep86
Richiep86

asked on

sql search as you type

this is a search as you type statement...

Currently, the system auto-selects the first result returned, if I try to enter “smith”, the system defaults to arrowsmith as that’s the first result returned.

Can i prevent this from happening?


select userid, (last_name + '; ' +first_name+' ['+UPPER(userid)+']') as appr_name 
from zusm_approvers 
where general_approver = 1 and userid not in ('%USER_ID%') and (last_name+';'+first_name like '%%%usersearchspec%%%' OR last_name+'; '+first_name like '%%%usersearchspec%%%' OR last_name+';'+first_name like '%%%usersearchspec%%%' OR last_name+','+first_name like '%%%usersearchspec%%%' OR first_name+';'+last_name like '%%%usersearchspec%%%' OR first_name+ SPACE(1)+last_name like '%%%usersearchspec%%%' )  ORDER BY last_name,first_name

Open in new window


THanks
Avatar of Sean Stuber
Sean Stuber

searching as you type isn't a sql function,  that's something your client software is doing for you.

Either it has already fetched the rows and is filtering on the fly,
or it's continually issuing new sql statements as you type
>>defaults to arrowsmith as that’s the first result returned

The '%' is a wildcard.  a % on the left says match ANY characters to the lect so 'smith' matches 'arrowsmith'.

If you do not want that, remove the left wildcards.
Avatar of Richiep86

ASKER

cheers guys....

i removed the wildcard and its still auto selecting the first match... any other ideas?
Based on your other question and syntax is this Oracle or SQL Server?
sql server i believe

cheers
ASKER CERTIFIED SOLUTION
Avatar of gavsmith
gavsmith
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I understand what you're saying.

Although when i write smith it automatically selects the first entry and automatically completes to the first match. This becomes a vicious circle. If i know the surname is smith but unaware of the first name I cannot see all the matches as it has selected the first name.

Its like Google selecting the first entry, therefore preventing matches to be shown.


Sounds like this could be a feature of the software I am using?

Thanks gav - got a better understanding now!
>> Sounds like this could be a feature of the software I am using?

Not sure if you can call it a feature!