/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Need Help Modifying SQL Code to Include Submitter's First Name and Last Name as Separate Variables

11/14/2018 530 views

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.

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

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

Answered 11/16/2018 by: jleitsch
Purple Belt

  • 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?
    • Swap the -negative number and change the delimiter
      Substring_Index(FULL_NAME, ',',-1) as Name,
      Substring_Index(FULL_NAME, ',',1) as Surname
      • 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

All Answers

0

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. 

Answered 11/15/2018 by: AbhayR
Red Belt

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ