SQL Server 2017 - Extracting source and target column names within a string containing column mappings
You did not specify your RDBMS - my solution uses SQL Server.

Your question states that you have a formatted string stored in text

col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;...


You further state


  I'm trying to come up with an elegant way of extracting and isolating
  all the xxx_source column names and all the xxx_target column names so
  I could store them in variables and get the following end result:
  
  @Source_Columns = 'col1_source,col2_source,col3_source'
  @Target_Columns = 'col1_target,col2_target,col3_target'




My solution uses SQL Server 2016 STRING_SPLIT. If you're not up to that version, there are other methods to split the string.  

drop table if exists #temp
Declare @String varchar(100) = 'col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;'
----------------------------
Declare @SourceColumns varchar(max)
Declare @TargetColumns varchar(max)

--Split on semicolon and use PARSENAME to extract the source and target column information
SELECT @SourceColumns = isnull(@SourceColumns + ',', '') + SourceColumn
    ,@TargetColumns = isnull(@TargetColumns + ',', '') + TargetColumn
FROM (
    SELECT parsename(replace(value, '|', '.'), 2) AS SourceColumn
        ,parsename(replace(value, '|', '.'), 1) AS TargetColumn
    FROM (
        SELECT *
        FROM String_split(@string, ';')
        ) a
    WHERE value &lt;&gt; ' '
    ) a




SELECT @SourceColumns

col1_source,col2_source,col3_source


SELECT @TargetColumns

col1_target,col2_target,col3_target