Trying to Make combinations within a bracket in SSMS column

  • Steve Collins

    Ten Centuries

    Points: 1075

    When I switch the datediff metric to nanoseconds and run the script over and over again the tally and cte are equal at 0 ns up to about 500 rows.  After 500 rows the tally is always faster.  Do you have some metrics which would support the cte being faster in certain cases?  Based on the script (which just a spot test) there's either no difference when row count is low or the tally is always faster when row count is high.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • carsten.saastamoinen

    SSC Veteran

    Points: 223

    I will stop now as it is obviously important for you to prove that tally is the only solution. Your tests are based on the fact that the function tally has already been compiled and cached. My argument is based on the fact that the tally function - hopefully - is used so little that it also has to be compiled when the tally version is used. So including the total compile and execution time for both solutions, the limit will be different from what you are arguing for - 23 ms. It is necessary to include all factors and as I wrote yesterday, I do not think that there are many who have a server with so much ram that all plans remain in cache - azure and non-azure! This will be the same as testing a query based on all data being loaded and no one else executing statement on the same server while testing.

  • Jeff Moden

    SSC Guru

    Points: 997104

    carsten.saastamoinen wrote:

    I will stop now as it is obviously important for you to prove that tally is the only solution. Your tests are based on the fact that the function tally has already been compiled and cached. My argument is based on the fact that the tally function - hopefully - is used so little that it also has to be compiled when the tally version is used. So including the total compile and execution time for both solutions, the limit will be different from what you are arguing for - 23 ms. It is necessary to include all factors and as I wrote yesterday, I do not think that there are many who have a server with so much ram that all plans remain in cache - azure and non-azure! This will be the same as testing a query based on all data being loaded and no one else executing statement on the same server while testing.

    Not sure who/whom you are addressing but I agree.  "It Depends".  If Azure is built in such a fashion that the compiled version of the Tally function is forced out of cache all the time but the need for it is frequent, then other considerations need to be made.  You also have to consider which is worse when such limited resources are available...  a 23 ms compile time or the number of reads the rCTE causes.

    The first consideration, though, would be to figure out why it's being forced out of cache so often.  In sounds like either there's way to little memory allocated to the system for what it needs to do or a preponderance of nasty code that is causing cache to clear way too often.

    If the code is being used to resolve much larger datasets than the paltry six or so we've been working with, so far, the 23ms can certainly be worth it.  Like I said, "It Depends".

    In all honesty, if this is a frequent problem with Azure, my next step would be to write a proposal to move to something else but I suspect the problem is not Azure itself.  A too low amount of memory or seriously performance/resource challenged code is more likely and that needs to be fixed instead having such small compiled code being forced out of memory all the time.

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jason A. Long

    SSC-Insane

    Points: 23711

    “One good test is worth a thousand expert opinions.” - Wernher Von Braun

    If anyone would like to do a 1M row test... Please feel free to use the following. It will produce 1M rows of randomly generated values ranging in length from 0 to 47 (including brackets) along with values with and without brackets.

    USE tempdb;
    GO

    IF OBJECT_ID('tempdb.dbo.TestData', 'U') IS NOT NULL
    BEGIN DROP TABLE dbo.TestData; END;

    CREATE TABLE dbo.TestData (
    id int NOT NULL,
    bracket_string varchar(50) NOT NULL,
    CONSTRAINT pk_testdata_id PRIMARY KEY CLUSTERED(id)
    );

    WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), -- 10
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b), -- 100
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n1 b), -- 10,000
    cte_Tally (n) AS (
    SELECT TOP (1000000)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM
    cte_n3 a CROSS JOIN cte_n3 b -- 100,000,000
    )
    INSERT dbo.TestData (id, bracket_string)
    SELECT
    id = t.n,
    --rnd.rnd1,
    --rnd.rnd2,
    --rnd.rnd3,
    --s1.string_1,
    --s2.string_2,
    --s3.string_3,
    bracket_string = CONCAT(s1.string_1, '[' + s2.string_2 + ']', s3.string_3)
    FROM
    cte_Tally t
    CROSS APPLY ( VALUES (
    ABS(CHECKSUM(NEWID())) % 10 ,
    ABS(CHECKSUM(NEWID())) % 30,
    ABS(CHECKSUM(NEWID())) % 5
    ) ) rnd (rnd1, rnd2, rnd3)
    CROSS APPLY (
    SELECT
    STRING_AGG(CHAR(cn1.char_num_1), '')
    FROM
    cte_Tally t1
    CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn1 (rnd_num_1)
    CROSS APPLY ( VALUES (CASE WHEN rn1.rnd_num_1 > 57 THEN rn1.rnd_num_1 + 7 ELSE rn1.rnd_num_1 END) ) cn1 (char_num_1)
    WHERE
    t1.n <= rnd.rnd1
    ) s1 (string_1)
    CROSS APPLY (
    SELECT
    STRING_AGG(CHAR(cn2.char_num_2), '')
    FROM
    cte_Tally t2
    CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn2 (rnd_num_2)
    CROSS APPLY ( VALUES (CASE WHEN rn2.rnd_num_2 > 57 THEN rn2.rnd_num_2 + 7 ELSE rn2.rnd_num_2 END) ) cn2 (char_num_2)
    WHERE
    t2.n <= rnd.rnd2
    ) s2 (string_2)
    CROSS APPLY (
    SELECT
    STRING_AGG(CHAR(cn3.char_num_3), '')
    FROM
    cte_Tally t3
    CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn3 (rnd_num_3)
    CROSS APPLY ( VALUES (CASE WHEN rn3.rnd_num_3 > 57 THEN rn3.rnd_num_3 + 7 ELSE rn3.rnd_num_3 END) ) cn3 (char_num_3)
    WHERE
    t3.n <= rnd.rnd3
    ) s3 (string_3);

    --===============================================================================================================================

    SELECT
    td.id,
    td.bracket_string
    FROM
    dbo.TestData td;

     

     

  • Jason A. Long

    SSC-Insane

    Points: 23711

    My previously posted code reformatted into an iTVF and replacing the tally function with an inline tally...

    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_NULLS ON;
    GO

    CREATE FUNCTION dbo.split_bracket_values
    /* ===================================================================
    07/25/2020 JL, Created: Just a test
    =================================================================== */
    --===== Define I/O parameters
    (
    @string varchar(50)
    )
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    SELECT
    combo_value = CONCAT(sec.beg, bv.bracket_val, sec.[end])
    FROM
    ( VALUES (
    NULLIF(CHARINDEX('[', @string), 0),
    NULLIF(CHARINDEX(']', @string), 0)
    ) ) br ([open], [close])
    CROSS APPLY ( VALUES (
    REPLACE(SUBSTRING(@string, 1, ISNULL(br.[open], 8000)), '[', ''),
    REPLACE(SUBSTRING(@string, br.[open], br.[close] - br.[open]), '[', ''),
    REPLACE(SUBSTRING(@string, ISNULL(br.[close], 8000), 8000), ']', '')
    ) ) sec (beg, mid, [end])
    CROSS APPLY (
    SELECT TOP (LEN(ISNULL(sec.mid, 0)))
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM ( VALUES
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ) x (n)
    ) t (n)
    CROSS APPLY ( VALUES (SUBSTRING(sec.mid, t.n, 1)) ) bv (bracket_val);
    GO

    Test results in next post...

     

  • Jason A. Long

    SSC-Insane

    Points: 23711

    My test harness...

    SET NOCOUNT ON;
    GO
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    PRINT(CONCAT(CHAR(13), CHAR(10), N' wait a moment...', CHAR(13), CHAR(10)));
    WAITFOR DELAY '00:00:01';
    GO
    DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
    PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
    ¦', LEFT(CONCAT(N' Start Time: ', @_clock_start, N' Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 1'),
    REPLICATE(N' ', 100)), 148), N'¦', CHAR(13), CHAR(10), N'+', REPLICATE(N'-', 148), N'+')); SET STATISTICS IO ON;
    -- ____________________________________________________________________________________________________________________________________________
    -- ?????? place tsql here ????????????????????????????????????????????????????????????
    DECLARE
    @cv varchar(50);

    SELECT
    @cv = sbv.combo_value
    FROM
    dbo.TestData td
    CROSS APPLY dbo.split_bracket_values(td.bracket_string) sbv

    -- ?????? place tsql here ????????????????????????????????????????????????????????????
    -- ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
    DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
    ¦', LEFT(STUFF(CONCAT(N' Finish Time: ', @_clock_stop, N' Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
    / 1000000.0, N' secs. ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'¦
    +', REPLICATE(N'-', 148), N'+'));
    GO

     

    And the results. Note that the best time (52 secs) was achieved by adding a nonclustered index keyed on the "bracket_string" column.

    first execution (cold cache with capture actual plan on and no index to prevent sort node)
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Start Time: 2020-07-25 13:53:16.5672257 Test Name: Test Query 1 ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 3828, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TestData'. Scan count 1, logical reads 4965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Finish Time: 2020-07-25 13:54:22.2230683 Duration: 65.655840 secs. 65655.843000 ms. ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Completion time: 2020-07-25T13:54:22.3406983-04:00


    second execution (cold cache with capture actual plan on and the following index: "CREATE NONCLUSTERED INDEX ix_td_bracketstring ON dbo.TestData(bracket_string)")
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Start Time: 2020-07-25 14:01:15.6915205 Test Name: Test Query 1 ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TestData'. Scan count 1, logical reads 4599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Finish Time: 2020-07-25 14:02:16.4727630 Duration: 60.781240 secs. 60781.243000 ms. ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Completion time: 2020-07-25T14:02:16.5635195-04:00

    3rd execution: cold cache DBCC DROPCLEANBUFFERS... no nc index.
    wait a moment...
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Start Time: 2020-07-25 14:10:07.1936631 Test Name: Test Query 1 ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 3828, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TestData'. Scan count 1, logical reads 4965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Finish Time: 2020-07-25 14:11:09.4280271 Duration: 62.234360 secs. 62234.364000 ms. ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Completion time: 2020-07-25T14:11:09.3477543-04:00

    4th execution: cold cache DBCC DROPCLEANBUFFERS... with nc index
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Start Time: 2020-07-25 14:13:45.4726695 Test Name: Test Query 1 ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Table 'Worktable'. Scan count 1400, logical reads 1907095, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TestData'. Scan count 1, logical reads 4599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Finish Time: 2020-07-25 14:14:37.7694618 Duration: 52.296790 secs. 52296.792000 ms. ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Completion time: 2020-07-25T14:14:37.7017084-04:00
  • Steve Collins

    Ten Centuries

    Points: 1075

    Jason A. Long the "s3 string" characters which appear after the brackets do not appear in any of the OP's example rows.  Imo that's a made up requirement which most of the solutions don't address.  I removed the s3 string and timestamped the code as follows

    IF OBJECT_ID('dbo.TestData', 'U') IS NOT NULL 
    BEGIN DROP TABLE dbo.TestData; END;
    go
    CREATE TABLE dbo.TestData (
    id int NOT NULL,
    bracket_string varchar(50) NOT NULL,
    CONSTRAINT pk_testdata_id PRIMARY KEY CLUSTERED(id)
    );
    go

    declare
    @test_start datetime2=sysutcdatetime();
    print ('TestData start: '+cast(@test_start as varchar));

    WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), -- 10
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b), -- 100
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n1 b), -- 10,000
    cte_Tally (n) AS (
    SELECT TOP (1000000)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM
    cte_n3 a CROSS JOIN cte_n3 b -- 100,000,000
    )
    INSERT dbo.TestData (id, bracket_string)
    SELECT
    id = t.n,
    --rnd.rnd1,
    --rnd.rnd2,
    --rnd.rnd3,
    --s1.string_1,
    --s2.string_2,
    --s3.string_3,
    bracket_string = CONCAT(s1.string_1, '[' + s2.string_2 + ']')
    FROM
    cte_Tally t
    CROSS APPLY ( VALUES (
    ABS(CHECKSUM(NEWID())) % 10 ,
    ABS(CHECKSUM(NEWID())) % 30,
    ABS(CHECKSUM(NEWID())) % 5
    ) ) rnd (rnd1, rnd2, rnd3)
    CROSS APPLY (
    SELECT
    STRING_AGG(CHAR(cn1.char_num_1), '')
    FROM
    cte_Tally t1
    CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn1 (rnd_num_1)
    CROSS APPLY ( VALUES (CASE WHEN rn1.rnd_num_1 > 57 THEN rn1.rnd_num_1 + 7 ELSE rn1.rnd_num_1 END) ) cn1 (char_num_1)
    WHERE
    t1.n <= rnd.rnd1
    ) s1 (string_1)
    CROSS APPLY (
    SELECT
    STRING_AGG(CHAR(cn2.char_num_2), '')
    FROM
    cte_Tally t2
    CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID())) % 36 + 48) ) rn2 (rnd_num_2)
    CROSS APPLY ( VALUES (CASE WHEN rn2.rnd_num_2 > 57 THEN rn2.rnd_num_2 + 7 ELSE rn2.rnd_num_2 END) ) cn2 (char_num_2)
    WHERE
    t2.n <= rnd.rnd2
    ) s2 (string_2);
    declare
    @test_end datetime2=sysutcdatetime();

    print ('TestData end: '+cast(@test_end as varchar));
    print ('Elapsed time: '+cast(datediff(s, @test_start, @test_end) as varchar));

    The result

    TestData start: 2020-07-25 18:57:54.8187398

    (1000000 row(s) affected)

    TestData end: 2020-07-25 18:59:29.7256354
    Elapsed time: 95

    So it takes 95 seconds to generate the million row test table.

    The itvf for splitting expanded to 50 characters:

    drop function if exists dbo.fnTIDprefix_Expand;
    go
    create function dbo.fnTIDprefix_Expand(
    @TIDprefix varchar(50))
    returns table with schemabinding as
    return
    select
    iif(br.br_start>0, concat(left(@TIDprefix, br.br_start-1), substring(@TIDprefix, n+br.br_start, 1)), @TIDprefix) TIDprefix_Expanded
    from
    (select
    charindex('[', @TIDprefix) br_start,
    charindex(']', @TIDprefix) br_end) br
    cross apply
    dbo.fnTally(1, iif(br.br_start>0, (br.br_end-br.br_start-1), 1)) t;
    go

    To run against the test table and insert into a temp table

    drop table if exists #TestData
    go
    CREATE TABLE #TestData (
    id int NOT NULL,
    split_string varchar(50) NOT NULL);
    go

    declare
    @split_start datetime2=sysutcdatetime();
    print ('Split start: '+cast(@split_start as varchar));

    insert #TestData
    select
    td.id,
    tx.TIDprefix_Expanded
    from
    dbo.TestData td
    cross apply
    dbo.fnTIDprefix_Expand(td.bracket_string) tx;

    declare
    @split_end datetime2=sysutcdatetime();

    print ('Split end: '+cast(@split_end as varchar));
    print ('Elapsed time: '+cast(datediff(s, @split_start, @split_end) as varchar));

    Results

    Split start: 2020-07-25 19:02:02.0859428

    (14543485 row(s) affected)

    Split end: 2020-07-25 19:02:42.6643727
    Elapsed time: 40

    So 95 seconds to create 1 million rows of test data and 40 seconds to split into 14,5 million rows in a temp table.

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jason A. Long

    SSC-Insane

    Points: 23711

    The OP bailed out of the conversation long ago but not before establishing a constant pattern of moving the goal posts. The "after string" simply presented a more robust solution.

    Your use of a nested function (dbo.fnTally) makes impossible to for anyone else to recreate and to a head to head test on the same machine. That is the reason I dumped the use use of dbo.tfn_Tally from my original code when creating my version of the function.

    Without any of the STASTICS_IO information, we can't even see if one function is doing more work than the other,

    With all that said, 40 secs is an excellent time on any machine, so well done. Especially considering that you are writing the values to a #temp table rather than doing a @variable dump.

  • Jason A. Long

    SSC-Insane

    Points: 23711

    Steve,

    After plugging in one of my in house tally functions (dbo.tfn_Tally_10k) into both functions, your version is MUCH faster.

    Using my function, on my DEV box, using a @variable dump... The time to execute your version dropped to 26 seconds., while mine remained in the 50 second range.

    Very nice!!!

    Here are some results using my harness...

            wait a moment...
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Start Time: 2020-07-25 16:32:11.7645910 Test Name: dbo.fnTIDprefix_Expand ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Table 'TestData'. Scan count 1, logical reads 4714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Finish Time: 2020-07-25 16:32:40.6084923 Duration: 28.843900 secs. 28843.901000 ms. ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+

    wait a moment...
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Start Time: 2020-07-25 16:32:42.6241246 Test Name: dbo.split_bracket_values ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    Table 'Worktable'. Scan count 280, logical reads 1874821, physical reads 0, read-ahead reads 3579, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TestData'. Scan count 1, logical reads 4714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    +----------------------------------------------------------------------------------------------------------------------------------------------------+
    ¦ Finish Time: 2020-07-25 16:33:41.4994099 Duration: 58.875280 secs. 58875.285000 ms. ¦
    +----------------------------------------------------------------------------------------------------------------------------------------------------+

    Completion time: 2020-07-25T16:33:41.4320004-04:00

     

     

  • Jeff Moden

    SSC Guru

    Points: 997104

    Steve Collins wrote:

    Jason A. Long the "s3 string" characters which appear after the brackets do not appear in any of the OP's example rows.  Imo that's a made up requirement which most of the solutions don't address.  

    Although I agree that the presence of String3 was not the OP's request(s), your code also produces things that are not in the OP's original or secondary posting of data.  There are times when only String1 is present and times when only string 2 is present. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Collins

    Ten Centuries

    Points: 1075

    Aaah that was true before I borrowed the charindex section of Phil's code.

    drop function if exists dbo.fnTIDprefix_Expand;
    go
    create function dbo.fnTIDprefix_Expand(
    @TIDprefix varchar(50))
    returns table with schemabinding as
    return
    select
    iif(br.br_start>0, concat(left(@TIDprefix, br.br_start-1), substring(@TIDprefix, n+br.br_start, 1)), @TIDprefix) TIDprefix_Expanded
    from
    (select
    charindex('[', @TIDprefix) br_start,
    charindex(']', @TIDprefix) br_end) br
    cross apply
    dbo.fnTally(1, iif(br.br_start>0, (br.br_end-br.br_start-1), 1)) t;
    go

    select * from dbo.fnTIDprefix_Expand('AC[123]')
    union all
    select * from dbo.fnTIDprefix_Expand('AXX');

    Results

    TIDprefix_Expanded
    AC1
    AC2
    AC3
    AXX

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Jeff Moden

    SSC Guru

    Points: 997104

    Steve,  I wasn't talking about the expansive output.  I was talking about the test code generator.

    Shifting gears, the OP provided no example of a left string having more than one character.  I wonder what the requirement for output would actually be for that because, technically and with any result > 2 characters, there is no way to tell what the left or right strings would be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 61 through 72 (of 72 total)

You must be logged in to reply to this topic. Login to reply