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
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
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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,
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,
ASKER
Do i need to ask another question? or can this be covered in the same thread?
many thanks.
many thanks.
That is due to the %'s
I.E search for smith
'%%%usersearchspec%%%'
will find *smith* (hence arrowsmith)
'usersearchspec%%%'
will find smith*
I.E search for smith
'%%%usersearchspec%%%'
will find *smith* (hence arrowsmith)
'usersearchspec%%%'
will find smith*
ASKER
That is correct but why does it always select arrowsmith as default?
Thanks gav!
Thanks gav!
Because you have
so therefore alphabetically arrowsmith comes before smith
Regards
ORDER BY last_name,first_name
so therefore alphabetically arrowsmith comes before smith
Regards
ASKER
doenst work if i use 'usersearchspec%%%'
cheers
cheers
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER