• You may be able to use the PARSENAME function but that depends on the data.
    😎

    USE TEEST;
    GO
    SET NOCOUNT ON;

    DECLARE @ChangeString VARCHAR(128) = '|LastName:Smith|Jones|FirstName:Bill|William';
    ;WITH REMOVE_LEADING_TOKEN(OUT_STR) AS
    (
      SELECT REPLACE(SUBSTRING(@ChangeString,2,128),'|','.') AS OUT_STR
    )
    SELECT
      PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,4),':','.'),2) AS COL_01
     ,PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,4),':','.'),1) AS COL_02
     ,PARSENAME(RLT.OUT_STR,3)           AS COL_03
     ,PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,2),':','.'),2) AS COL_04
     ,PARSENAME(REPLACE(PARSENAME(RLT.OUT_STR,2),':','.'),1) AS COL_05
     ,PARSENAME(RLT.OUT_STR,1)           AS COL_06
    FROM  REMOVE_LEADING_TOKEN  RLT;

    Output

    COL_01    COL_02  COL_03 COL_04     COL_05  COL_06
    --------- ------- ------ ---------- ------- --------
    LastName  Smith   Jones  FirstName  Bill    William