Function to split string

  • Kenean

    Say Hey Kid

    Points: 661

    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 4 weeks ago by  Kenean.
  • Phil Parkin

    SSC Guru

    Points: 244662

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeff Moden

    SSC Guru

    Points: 996843

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Sergiy

    SSC Guru

    Points: 109784

    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?

  • Jeff Moden

    SSC Guru

    Points: 996843

    😀

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Sergiy

    SSC Guru

    Points: 109784

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720484

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

     

Viewing 9 posts - 1 through 9 (of 9 total)

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