Sql-server – SQL Server 2017 – Extracting source and target column names within a string containing column mappings

parsesql-server-2017stringstring manipulation

I have the following format string stored in text (could be any number of columns):

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

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'

At the end of the day, I'd like to perform SELECTs on my source and target columns to perform data compares.

This is what I've achieved so far, but I find it's just too complex for nothing (with a table valued function):

CREATE FUNCTION [dbo].[UF_miscParseStringToTable]
    (
        @list   nvarchar(MAX)
        , @sep  varchar(8) 
    ) 
RETURNS @ts table
    (
        [ID]        int identity
        , [value]   nvarchar(MAX)
    )
AS 
BEGIN 

    -- Parameters check
    if ((@sep is null) or (datalength(@sep) < 1)) return 

    if ((@list is null) or (@list = '') or (@list = @sep)) return

    -- Add path wildcards directly with sep
    -- ?worth it?
    if (left(@sep, 1) <> '%')   set @sep = '%' + @sep
    if (right(@sep, 1) <> '%')  set @sep = @sep + '%'

    -- First first sep
    declare @i int
    set @i = patindex(@sep, @list)

    -- Acc values
    while (@i > 0) begin
        insert into @ts ([value]) values (rtrim(left(@list, @i - 1)))
        set @list = ltrim(right(RTRIM(@list), len(@list) + 3 - (@i + len(@sep) )))
        set @i = patindex(@sep, @list)
    end

    set @list = rtrim(@list)
    -- Insert last value, if any
    if (@list <> '') insert into @ts (value) values (@list)

    return

END

The function above basically takes my mapping string and converts it to a list of column names in a table (see query logic below):

DECLARE @Delim varchar(1) = '|'
DECLARE @Mapping varchar(max) = 'col1_source|col1_target;col2_source|col2_target;col3_source|col3_target'  
DECLARE @String varchar(max) = REPLACE(@Mapping,';', @Delim)
SELECT * FROM dbo.UF_miscParseStringToTable(@String, @Delim)

The above resulting query yields the following table:

 ID| value
 1 | col1_source
 2 | col1_target
 3 | col2_source
 4 | col2_target
 5 | col3_source
 6 | col3_target

I could perhaps do a join on the column indexes but, I'm finding it difficult to isolate my source and target fields so that I could perform data comparisons between them. In addition, I'd like to avoid performing an extra join to a table if I don't have to.

Here are the results desired (to be able to perform the following):

SELECT col1_source, col2_source, col3_source FROM mytable;

SELECT col1_target, col2_target, col3_target FROM mytable;

Any help or ideas would be great!

Shawn

Best Answer

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 <> ' '
    ) a

SELECT @SourceColumns

col1_source,col2_source,col3_source

SELECT @TargetColumns

col1_target,col2_target,col3_target

Related Solutions

Sql-server – Find rows with similar string values

Having dealt this issue, the most effective and performant way is to create a CLR function that calculates the Levenshtein Distance. You will be able to mark the assembly as SAFE (if you're at all concerned about security), and it runs much quicker than SOUNDEX() or any in-built SQL Server functions.

Here's the code to set up the assembly and function in the database, as well as a basic version of the Levenshtein Distance algorithm implemented in C# from https://en.wikibooks.org/wiki/Algorithm_Implementation/Strings/Levenshtein_distance#C#

C#:

using System;
using System.Security.Cryptography;

namespace LevenshteinDistance
{
    public class LevenshteinDistance
    {
        private int LevenshteinDistance(string a, string b)
        {
            if (string.IsNullOrEmpty(a))
            {
                if (!string.IsNullOrEmpty(b))
                {
                    return b.Length;
                }
                return 0;
            }

            if (string.IsNullOrEmpty(b))
            {
                if (!string.IsNullOrEmpty(a))
                {
                    return a.Length;
                }
                return 0;
            }

            int cost;
            int[,] d = new int[a.Length + 1, b.Length + 1];
            int min1;
            int min2;
            int min3;

            for (int i = 0; i <= d.GetUpperBound(0); i += 1)
            {
                d[i, 0] = i;
            }

            for (int i = 0; i <= d.GetUpperBound(1); i += 1)
            {
                d[0, i] = i;
            }

            for (int i = 1; i <= d.GetUpperBound(0); i += 1)
            {
                for (int j = 1; j <= d.GetUpperBound(1); j += 1)
                {
                    cost = (a[i-1] != b[j-1])? 1 : 0; 

                    min1 = d[i - 1, j] + 1;
                    min2 = d[i, j - 1] + 1;
                    min3 = d[i - 1, j - 1] + cost;
                    d[i, j] = Math.Min(Math.Min(min1, min2), min3);
                }
            }
            return d[d.GetUpperBound(0), d.GetUpperBound(1)];
        }        
    }
}

T-SQL:

use [master];
go

exec sp_configure 'clr enabled', 1;
go
reconfigure with override;
go

use [database_name];
go

-- Drop the function...
if exists (select 1 from sys.objects so where so.[name] = 'LevenshteinDistance')
    drop function dbo.LevenshteinDistance;
go

-- ...then the assembly
if exists (select 1 from sys.assemblies sa where sa.[name] = 'LevenshteinDistance')
    drop assembly [LevenshteinDistance];
go

-- Now load the assembly from an appropriately accessible location
create assembly [LevenshteinDistance]
from
    'd:\LevenshteinDistance.dll'
with
    permission_set = safe;
go

-- Create an asymmetric key from the assembly file
use [master];
go

if not exists (select 1 from sys.asymmetric_keys ak where ak.[name] = 'LevenshteinDistanceKey')
begin
    create asymmetric key LevenshteinDistanceKey
    from executable file = 'd:\LevenshteinDistance.dll';
end
go

-- Create a user to associate with the assembly from the asymmetric key, and then
-- revoke connect access. The login is used to execute the assembly.
use [master];
go

if not exists (select 1 from sys.server_principals sp where sp.[name] = 'LevenshteinDistanceKeyUser')
begin
    create login LevenshteinDistanceKeyUser from asymmetric key LevenshteinDistanceKey;
    revoke connect sql from LevenshteinDistanceKeyUser;
end
go

grant external access assembly to LevenshteinDistanceKeyUser;
go

use [database_name];
go
alter assembly [LevenshteinDistance] with permission_set = safe;
go

-- Create the SQL function which will be called
create function [dbo].LevenshteinDistance
(
    @string1 nvarchar(2048)
    ,@string2 nvarchar(2048)
)
returns nvarchar(max)
as
    external name LevenshteinDistance.[LevenshteinDistance.LevenshteinDistance].LevenshteinDistance;
go

Sql-server – Use STUFF function to insert at multiple points in a string

You need to get it as rows before you can use STUFF.

DECLARE @rows nvarchar(max) = '[A],[B],[C]';
DECLARE @cols nvarchar(max);

SELECT LTRIM(RTRIM(j.value)) FROM STRING_SPLIT(@rows, ',') j

SET @cols = STUFF((SELECT ', ' + (col) 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ')') 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;

SET @cols = STUFF((SELECT ', ' + ('SUM(' + col + ') ' + col) 
                   FROM (SELECT LTRIM(RTRIM(j.value)) col FROM STRING_SPLIT(@rows, ',') j) Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,2,'');

SELECT @cols;
GO

| (No column name) |
| :--------------- |
| [A]              |
| [B]              |
| [C]              |

| (No column name) |
| :--------------- |
| [A], [B], [C]    |

| (No column name)             |
| :--------------------------- |
| SUM([A]), SUM([B]), SUM([C]) |

| (No column name)                         |
| :--------------------------------------- |
| SUM([A]) [A], SUM([B]) [B], SUM([C]) [C] |

dbfiddle here

Best Answer

Related Solutions

Sql-server – Find rows with similar string values

Sql-server – Use STUFF function to insert at multiple points in a string

Related Question