sql search as you type

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this.....

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+' '+last_name
       like '%%%usersearchspec%%%' )
        ORDER BY last_name,first_name
Commented:
Try taking out the extra quote marks:

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


You had put the TSQL Space function in quotes which would create the string: firstname+SPACE(1)lastname

Author

Commented:
Great work!
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

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

Author

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

many thanks.

Commented:
That is due to the %'s

I.E search for smith

'%%%usersearchspec%%%'

will find *smith* (hence arrowsmith)

'usersearchspec%%%'

will find smith*

Author

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

Thanks gav!

Commented:
Because you have

ORDER BY last_name,first_name

Open in new window


so therefore alphabetically arrowsmith comes before smith

Regards

Author

Commented:
doenst work if i use 'usersearchspec%%%'

cheers

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