Good way to write STRING SPLIT

  • (select STRING_AGG(cg2.ind_name, ', ') within group (order by cg2.ind_id) 
    from dbo.ind_lp cg2 with(nolock) where cg2.ind_id in (SELECT DISTINCT CAST([value] AS int) AS id
    FROM STRING_SPLIT(um.ind_group, ','))) as ind_group,

    Is there a better way to write this.

    ind_group has multiple value (like 45, 56, 798, etc )

     

     

     

  • I can't see your data, so it's hard to tell. Can you post some sample data... CREATE TABLE script, INSERTS... so we have some idea what your data looks like?

  • What is the object um in your code, as it's not defined in the SQL snippet you have given us. Ideally, however, you should be trying to fix the design; storing delimited data means you don't have any foreign key constraints, nor (likely) any validation on the value(s)  in the column; so your CAST([value] AS int) could easily fail as some (foolishly) inserted the value '1,2,e'.

    Separate question, why the NOLOCK here as well? If you have performance issue, I would be looking to fix those. NOLOCK isn't a "magic go faster button", and it can make the results you get unreliable. Are you happy with such results?

    Thom~

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

  • ind_lp data looks like:

    id   ind_name

    1   abc & (11)

    2  bglh (45)

    3  yyiouiopo(67)

    ...

     

    UM is the lot many cols

    ind_grp

    30608,30624,30644,30645

    30601,30650,30623,30624,30637,30641

    30623,30631,30645

    ...

     

    Pls ignore the nolock, i will be removing it when submitting the code.

  • pietlinden wrote:

    I can't see your data, so it's hard to tell. Can you post some sample data... CREATE TABLE script, INSERTS... so we have some idea what your data looks like?

    Please do this. Make it possible for people to paste your sample DDL/DML into SSMS and run it.

    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.

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

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