Good way to write STRING SPLIT

Question

Good way to write STRING SPLIT

SSCarpal Tunnel

Points: 4591

April 7, 2021 at 10:35 am

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

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

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

pietlinden SSC Guru Points: 63506 More actions · Answer 1

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?

Thom A SSC Guru Points: 99133 More actions · Answer 2

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

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 3

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.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel