Records on one field - SQL Server

Question

Hi Developers

I want to understand how do I achieve this

i have records that have id numbers

i want to split the second record to have unique id's

is this achievable , if yes may i ask how ?

thank you

Answer 1

Hi @Ronald Van Der Westhuizen ，

You could create a split function like String_Split as below:

CREATE FUNCTION [dbo].[SplitString]  
(  
    @List NVARCHAR(MAX),  
    @Delim VARCHAR(255)  
)  
RETURNS TABLE  
AS  
    RETURN ( SELECT [Value] FROM   
      (   
        SELECT   
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
          FROM sys.all_objects) AS x  
          WHERE Number <= LEN(@List)  
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
      ) AS y  
	      );  

Then call this function as below:

declare @records table  
(  
ID_Number int,  
ID varchar(100),  
Name varchar(100),  
surname varchar(100)  
)  
  
insert into @records values  
(1,'3','Sam','Matt'),  
(2,'4,5','Jones','Jenson'),  
(3,'9','Carl','Smith')  
  
SELECT ID_Number,value, name,  surname  
FROM @records    
    CROSS APPLY [dbo].[SplitString](ID, ',');   

Output:

ID_Number	value	name	surname  
1	3	Sam	Matt  
2	4	Jones	Jenson  
2	5	Jones	Jenson  
3	9	Carl	Smith  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Since you didn't show us the result you want, this is somewhat of a guess at what you want,

Declare @Sample Table(IDNumber int, ID varchar(20), Name varchar(20), surname varchar(20));
Insert @Sample(IDNumber, ID, Name, surname) Values
(1, '3', 'Sam', 'Matt'),
(2, '4,5', 'Jones', 'Jenson'),
(3, '9', 'Carl', 'Jenson');

Select s.IDNumber, ss.value As ID, s.Name, s.surname
From @Sample s
Cross Apply String_Split(s.ID, ',') ss;

The above code requires that you are on SQL Server 2016 or later.

Tom

Answer 3

WOW
Thank you so much really helpful

Answer 4

Before SQL 2016 SQL Server did not have a built in function to split strings. So it was necessary to create your own user defined function. You can find lots of them by searching for "SQL String Split". One example is

CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
RETURNS table
/* Use Option(MaxRecursion 0) in queries that call this function if 
   there can be more than 99 delimited values in @DelimitedString */
AS
RETURN (
    WITH Pieces (ID, start, stop) AS (
      SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
      UNION ALL
      SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT ID,
      SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
    FROM Pieces
  )
GO

Declare @Sample Table(IDNumber int, ID varchar(20), Name varchar(20), surname varchar(20));
Insert @Sample(IDNumber, ID, Name, surname) Values
(1, '3', 'Sam', 'Matt'),
(2, '4,5', 'Jones', 'Jenson'),
(3, '9', 'Carl', 'Jenson');

Select s.IDNumber, ss.Element As ID, s.Name, s.surname
From @Sample s
Cross Apply (Select Element From dbo.Split(s.ID, ',')) ss;

Tom