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
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
gavsmith

8/22/2022 - Mon
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
slightwv (䄆 Netminder)

>>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.
Richiep86

ASKER
cheers guys....

i removed the wildcard and its still auto selecting the first match... any other ideas?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

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

ASKER
sql server i believe

cheers
ASKER CERTIFIED SOLUTION
gavsmith

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Richiep86

ASKER
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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gavsmith

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

Not sure if you can call it a feature!