Get firstname and lastname from domain account

• lanky_doodle

  SSCrazy

  Points: 2378

  More actions

  March 8, 2019 at 9:18 am

  #379737

  Hi,

  As title really!

  DOMAIN\Joe.Bloggs should be Firstname: Joe and Lastname: Bloggs

  I'm currently doing lots of funky substrings:

  select        substring(SYSTEM_USER, charindex('\', SYSTEM_USER, 0) +1, charindex('.', SYSTEM_USER) - charindex('\', SYSTEM_USER, 0) -1),
                substring(SYSTEM_USER, charindex('.', SYSTEM_USER, 0) +1, len(SYSTEM_USER) - charindex('.', SYSTEM_USER, 0));
  
  Is there a neater way?!

  Thanks!

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 8, 2019 at 9:36 am

  #2023954

  DECLARE @SYSTEM_USER sysname = 'DOMAIN\Joe.Bloggs';
WITH SystemUser (UserName) AS (
    SELECT REPLACE(@SYSTEM_USER,'\','.')
    )
SELECT
     PARSENAME(UserName,2) AS FirstName
,    PARSENAME(UserName,1) AS LastName
FROM SystemUser;

  John