Discrepancy in SDM Contacts Search Result when searched by userid

Products

• CA Service Management - Service Desk Manager

Issue/Introduction

When a contact is searched with its userid containing "_" , the application doesnt differentiate between "_" and "."

Ex: Userid=xyz_xyz and Userid=xyz.xyz is considered as the same, which ideally is incorrect.

Servicedesk tab-> search->contact : 
 # UserID= xyz.xyz%....   This will list exactly the contact which are in xyz.xyz format. 
 # UserId= xyz_xyz%.....  This will list all the contact with the user id xyz. Requirement is to populate only xyz_xyz

Environment

Release : 17.3

Component :SDM-Classic UI

Resolution

This is a SQL Server Limitation In the where clause that we build for the contact page we use the "XXX LIKE YYY" syntax for the where clause.

In SQL Server with the LIKE syntax the underscore character is considered a wildcard, hence any one character is matched. To make the search more specific and make the underscore behave like a underscore character the customer can use [_]. 

 # UserID= xyz[_]xyz%....   This will list exactly the contact which are in xyz_xyz format. 

Sample Sql Code: 

SELECT ca_contact.userid, ca_contact.last_name+CASE WHEN ( ca_contact.first_name IS NULL OR ca_contact.first_name = '' ) AND ( ca_contact.middle_name IS NULL OR ca_contact.middle_name = '' ) THEN N'' ELSE N', '+isnull(ca_contact.first_name,N'')+N' '+isnull(ca_contact.middle_name,N'') END, ca_contact.last_name, ca_contact.first_name, ca_contact.middle_name, ca_contact.pri_phone_number, ca_contact.alternate_identifier, ca_contact.last_update_date, ca_contact.contact_uuid FROM ca_contact WHERE ca_contact.userid LIKE 'test[_]test' AND ca_contact.inactive = 0 ORDER BY 1 ASC