Need Help Modifying SQL Code to Include Submitter's First Name and Last Name as Separate Variables
Does anyone know how to modify the below SQL code to include submitter's first name and last name as separate variables? I tried to modify it, but it came up with errors.
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
Full KACE article: https://support.quest.com/kace-systems-management-appliance/kb/111222/how-to-notify-any-user-or-group-by-email-when-a-new-ticket-is-created
Any help will be highly appreciated.
Answers (2)
Top Answer
As long as all your "FULL_NAME" fields are structured like "FIRST_NAME LAST_NAME" then this will work:
select Substring_Index(FULL_NAME, ' ',1) as Name,
Substring_Index(FULL_NAME, ' ',-1) as Surname
From USER
Comments:
-
Thank you for suggestion. Unfortunately, our "FULL_NAME" fields are structured like "LastName, FirstName,". If we use "Dear $submitter_fname" in the email template, it will be converted to "Dear DOE, JOHN," and that's the reason why we want to separate them into 2 variables. Any thoughts? - eric0626 5 years ago
-
Swap the -negative number and change the delimiter
Substring_Index(FULL_NAME, ',',-1) as Name,
Substring_Index(FULL_NAME, ',',1) as Surname - jleitsch 5 years ago-
Thank you very very much jleitsch!!!!! It works!!!!! In case anyone needs to do the same thing. Please see the actual code below.
Substring_Index(SUBMITTER.FULL_NAME, ',',1) as SUBMITTER_LASTNAME, -- $submitter_lastname
Substring_Index(SUBMITTER.FULL_NAME, ',',-1) as SUBMITTER_FIRSTNAME, -- $submitter_firstname - eric0626 5 years ago
There is no field which keeps first name and last name as separate fields. The data comes from user record which doesn't keep them separate.
