Sfrgttk

How to write painful torture scenes without being over-the-top

How should I ship cards?

Which was the first story to feature space elevators?

Is it ethical to apply for a job on someone's behalf?

Found a major flaw in paper from home university – to which I would like to return

我可不觉得 - agree or disagree?

Is Apex Sometimes Case Sensitive?

Could Comets or Meteors be used to Combat Global Warming?

What is formjacking?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

Ramanujan's radical and how we define an infinite nested radical

Is opening a file faster than reading variable content?

Last Reboot commands don't agree

How to read the error when writing vector files in QGIS 3.0

Stream.findFirst different than Optional.of?

What dissuades people from lying about where they live in order to reduce state income taxes?

Why is quixotic not Quixotic (a proper adjective)?

Can "ee" appear in Latin?

Why does finding small effects in large studies indicate publication bias?

How to achieve physical gender equality?

80-bit collision resistence because of 80-bit x87 registers?

Almost normal subgroup

Taking an academic pseudonym?

Why is ra lower than re while la is higher than le?

Extracting source and target column names within a string containing column mappings

Parsing and binding variables for SELECT .. WHERE column IN ( .. ) queriesUsing an index for both asc and desc on a string columnSQL Server - NTEXT columns and string manipulationnested if expressions for string in excel source file in derived column expressionPerformance gap between WHERE IN (1,2,3,4) vs IN (select * from STRING_SPLIT('1,2,3,4',','))Use STUFF function to insert at multiple points in a string?SELECT i.c.w. a COUNT and INNER JOIN gives different results with string literals and numeric values when they are used in a WHERE clause

0

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):

ALTER 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)DECLARE @Result NVARCHAR(max)

    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.

Any help or ideas would be great!
Shawn

string string-manipulation parse

share

asked 6 mins ago

shawnyshawnyshawnyshawny

62

add a comment | 

0

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):

ALTER 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)DECLARE @Result NVARCHAR(max)

    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.

Any help or ideas would be great!
Shawn

string string-manipulation parse

share

asked 6 mins ago

shawnyshawnyshawnyshawny

62

add a comment | 

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):

ALTER 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)DECLARE @Result NVARCHAR(max)

    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.

Any help or ideas would be great!
Shawn

string string-manipulation parse

share

asked 6 mins ago

shawnyshawnyshawnyshawny

62

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

ALTER 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

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)DECLARE @Result NVARCHAR(max)

    SELECT * FROM dbo.UF_miscParseStringToTable(@String, @Delim)

 ID| value

 1 | col1_source

 2 | col1_target

 3 | col2_source

 4 | col2_target

 5 | col3_source

 6 | col3_target

share

asked 6 mins ago

shawnyshawnyshawnyshawny

62

share

asked 6 mins ago

shawnyshawnyshawnyshawny

62

asked 6 mins ago

shawnyshawnyshawnyshawny

62

asked 6 mins ago

shawnyshawnyshawnyshawny

62