Function to split string

  • I have this function, but I wanted to pass a table so as to use the same function to get the job done for multiple tables. For example, I want this function work for table1, and table2. But it is just for table1 currently. I was trying to use a dynamic sql in vain; it doesn't pass the parameter selected. Can someone help? Give me guide on how to pass table as a parameter.

    Sample data, table1

    CREATE TABLE table1 (id int identity (1,1), name varchar(60)) 
    INSERT INTO table1
    VALUES ('a1, a2, a9, a8')

    Sample data, table2

    CREATE TABLE table2 (id int identity (1,1), name varchar(60)) 
    INSERT INTO table2
    VALUES ('a1, a2, a9, a8')

    The function:

    CREATE FUNCTION f_split
    (@id INT)
    RETURNS @ab
    TABLE (name VARCHAR(20),
    ab1 VARCHAR(5)
    )
    AS
    BEGIN
    DECLARE @temp TABLE (rn INT, name VARCHAR(5))
    INSERT INTO @temp(rn, name)
    SELECT ROW_NUMBER() OVER(ORDER BY LTRIM(RTRIM(Split.a.value('.', 'NVARCHAR(MAX)'))) ASC) rn, LTRIM(RTRIM(Split.a.value('.', 'NVARCHAR(MAX)'))) Result
    FROM
    (
    SELECT CAST('<X>'+REPLACE([name], ',', '</X><X>')+'</X>' AS XML) AS String
    FROM table1 where id = @id
    ) AS A
    CROSS APPLY String.nodes('/X') AS Split(a)
    ORDER BY 1
    INSERT INTO @ab
    SELECT * FROM @temp
    RETURN
    END

    This gives the result from table1.

    SELECT * FROM F_SPLIT(1)

    But I want the same function to work for table2 as well.

    Any help is appreciated.

     

     

     

    • This topic was modified 3 years, 8 months ago by  Kenean.
  • You can't use dynamic SQL in functions. But I think that dynamic SQL is the only way that you'll achieve this, so you probably need to rethink. Can you use a stored proc instead?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • If I am reading your function correctly - all it is doing is splitting the delimited string and returning the values as rows.  There is no reason to do this since we now have STRING_SPLIT or other string split utilities available.

    And - this XML method of splitting strings is quite slow.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    If I am reading your function correctly - all it is doing is splitting the delimited string and returning the values as rows.  There is no reason to do this since we now have STRING_SPLIT or other string split utilities available.

    Not so fast, Jeff... if you look at the code the OP wrote, it's returning an ordinal position for the split-out elements.  STRING_SPLIT doesn't return such a thing and, since MS does NOT guarantee the order of the returned split-out elements, you can't rely on tricks like ROWNUMBER() to create such ordinals.

    Of course, it looks like the OP is also generating the ordinals incorrectly, as well. 😀

    Jeffrey Williams wrote:

    And - this XML method of splitting strings is quite slow.

    Totally agreed on that!

    --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.


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

  • Jeff Moden wrote:

    Jeffrey Williams wrote:

    If I am reading your function correctly - all it is doing is splitting the delimited string and returning the values as rows.  There is no reason to do this since we now have STRING_SPLIT or other string split utilities available.

    Not so fast, Jeff... if you look at the code the OP wrote, it's returning an ordinal position for the split-out elements.  STRING_SPLIT doesn't return such a thing and, since MS does NOT guarantee the order of the returned split-out elements, you can't rely on tricks like ROWNUMBER() to create such ordinals.

    Of course, it looks like the OP is also generating the ordinals incorrectly, as well. 😀

    Jeffrey Williams wrote:

    And - this XML method of splitting strings is quite slow.

    Totally agreed on that!

    Or other utilities...like DelimitedSplit8K that someone wrote a fantastic article about, if I could just remember who 🙂

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Using a function to parse string arrays stored in 2 or more different tables containing homogeneous data - how many more wrong approaches can you fit into such a simple task?

    _____________
    Code for TallyGenerator

  • 😀

     

    --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.


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

  • Whoever removed my post - are you sure it was really a spam?

    In the very first reply Phil suggested “you probably need to rethink”. And I just pointed out that there is more than one point to rethink here.

    _____________
    Code for TallyGenerator

  • Unspammed post. It might have been a report from a user or some glitch in the automated process. Either way, it's fixed.

     

  • You can preserve order by creating a table with an identity column and then inserting values from string_split() into that table, like this:

    Create Function [dbo].[fn_PreserveOrder]
    (
    @InColumn varchar (max)
    ,@Delimiter varchar (1)
    )
    Returns @Columns TABLE (RowName varchar (20), Value varchar (max))
    As
    Begin

    Declare @OrderedTable Table (RowNum bigint Identity, Value varchar (max))
    Insert into @OrderedTable select value from string_split(@InColumn, @Delimiter)

    Insert Into @Columns select Concat('Value', RowNum), Value from @OrderedTable

    Return
    End

    Test it:

    select * from dbo.fn_PreserveOrder('1,9,7,8,3', ',')

    Results:

    RowNameValue
    Value11
    Value29
    Value37
    Value48
    Value53

     

    Hope that helps.

  • Again, just to say it out loud... use such methods at your own risk because MS has stated that there is to guaranteed output order from STRING_SPLIT().  I've not run into anyone where it has become a problem but, if it does, MS is simply going to repeat that the order is not guaranteed.

    --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.


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

  • It works until it’s below the parallel execution threshold. You never tested it on really long strings, like 10k elements, did you?

    once the parallelism kicks in the order of the elements inserted into a table - permanent or temporary - won’t be preserved anymore.

    _____________
    Code for TallyGenerator

  • I was thinking the same thing but I've not yet seen it go parallel.  That's most likely because I don't use it for much of anything because it does require special handling to produce the ordered ordinals for the returned elements and it's just easier to use what I built previously, which is still very fast especially after adding in the "Lead" optimization the Eirikur Eiriksson wrote about for systems using SQL Server 2012 and up.

    --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.


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

  • Jeff Moden, you mention what you built previously.  Did I overlook a code sample or link?

    Okay, how about this?  It doesn't use string_split().  I've tested it to 40,000 values several times and it took about 7 seconds to return the desired result.  (When I tried to build a string of 50,000 values, I waited 20 seconds and killed it.  That's building the string, not splitting it.)

    Anyway,

    Create Function [dbo].[fn_SplStrPrsvOrder](@str nvarchar (max), @dlm varchar (1))

    Returns @Values TABLE (RowNum int identity, Value nvarchar (max))
    as

    Begin

    Declare @i bigint = 1
    Declare @out varchar(max) = ''

    Set @i = 1

    While @i < len(@str) + 1
    Begin
    Begin
    Set @out = Concat(@out, Substring(@str, @i, 1))
    If Substring(@str, @i, 1 ) = @dlm
    Begin
    Insert into @Values Select Left(@out, Len(@out) - 1)
    Set @out = ''
    End
    If @i = Len(@str)
    Begin
    Insert into @Values Select Left(@out, Len(@out))
    End
    Set @i = @i + 1
    Continue
    End
    Set @i = @i + 1
    End

    Return
    End

    Test it:

    -- build test string
    Declare @str nvarchar(max) = ''
    Declare @x bigint = 1
    While @x < 40001
    Begin
    Set @str = Concat(@str, convert(varchar (10), @x), ',')
    Set @x = @x + 1
    End

    Set @str = Left(@str, Len(@str) - 1)
    Declare @dlm varchar (1) = ','

    Select Value from dbo.fn_SplStrPrsvOrder(@str, @dlm) order by RowNum

    I have read a little about parallelism but I don't claim to grok it in fullness.  Maybe I just got lucky in my limited number (maybe 12 or 15) of trials.

    Rick

  • Goffr wrote:

    Jeff Moden, you mention what you built previously.  Did I overlook a code sample or link?

    Jeff would be referring to their very well known DelimitedSplit8k function, which also lead to the DelimitedSplit8k_LEAD function.

    Goffr wrote:

    Okay, how about this? 

    Hmm, that's a multi-line table-value function; they're known for their poor performance. Add a WHILE in there, and it's going to be a very  slow contender I'm afraid. Don't forget, SQL is a set based language, so using looping processes are something you want to avoid unless you have very good reason to.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 15 posts - 1 through 15 (of 20 total)

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