sql search as you type

Richiep86
Richiep86 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
cheers guys....

i removed the wildcard and its still auto selecting the first match... any other ideas?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Based on your other question and syntax is this Oracle or SQL Server?

Author

Commented:
sql server i believe

cheers
Commented:
>> i removed the wildcard and its still auto selecting the first match... any other ideas?

Auto selecting the first match is how your software is designed, I don't think you are going to get the solution you are after by changing the SQL query because the search results could be limitless.

What your expecting is similar to saying everytime you do a search in google you want the page you actually want to be the first one it finds!! So google would only have to return one result. Would be nice but its impossible.

What you would want is your software doing something similar to JQuery's autocomplete which gives you a selection of names from the filter instead of just choosing the first one it returns. Alternatively only choosing the first returned row if there is only one match otherwise it could be wrong.

Hope that helps

Author

Commented:
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!

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

Not sure if you can call it a feature!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial