Avatar of Richiep86
Richiep86
 asked on

sql search as you type

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


the above does not search by first_name SPACE last_name...

Customers should be able to type their first name followed by a space to search their name.

i have tried this:

OR first_name+'+ SPACE(1)'+last_name like '%%%usersearchspec%%%'
OR first_name+' '+last_name like '%%%usersearchspec%%%'

with no luck,

Any ideas?
Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005Oracle Database

Avatar of undefined
Last Comment
Richiep86

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Saurabh Bhadauria

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
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
Great work!
Richiep86

ASKER
Could you also tell me why the system auto-selects the first result returned?

If a user tries to search “smith”, the system defaults to arrowsmith as that’s the first result returned?

Is there anyway (editing the exisiting code) to stop the search auto selecting the first result?

Many thanks,
Richiep86

ASKER
Do i need to ask another question? or can this be covered in the same thread?

many thanks.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
gavsmith

That is due to the %'s

I.E search for smith

'%%%usersearchspec%%%'

will find *smith* (hence arrowsmith)

'usersearchspec%%%'

will find smith*
Richiep86

ASKER
That is correct but why does it always select arrowsmith as default?

Thanks gav!
gavsmith

Because you have

ORDER BY last_name,first_name

Open in new window


so therefore alphabetically arrowsmith comes before smith

Regards
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Richiep86

ASKER
doenst work if i use 'usersearchspec%%%'

cheers