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