SQL How to split a single column into multiple variable columns

Question

I am working on MSSQL trying to split a single column of a row into multiple columns. The string column contains numbers separated by semicolons, for example:

190230943204;190234443204; 

However, some rows have more numbers than others, so in the database you can have

 190230943204;190234443204; 121340944534;340212343204;134530943204 

I have seen some solutions for dividing a single column into a certain number of columns, but not variable columns. Columns with less data (2 rows of rows separated by commas instead of 3) will have zero values ​​in third place.

Ideas? Let me know if I clarify anything.

Answer 1

Dividing this data into separate columns is a very good start (values ​​separated by a coma are heresy). However, a "variable number of properties" should usually be modeled as a one-to-many relationship .

 CREATE TABLE main_entity ( id INT PRIMARY KEY, other_fields INT ); CREATE TABLE entity_properties ( main_entity_id INT PRIMARY KEY, property_value INT, FOREIGN KEY (main_entity_id) REFERENCES main_entity(id) ); 

entity_properties.main_entity_id is the foreign key to main_entity.id .

Congratulations, you are on the right track, this is called normalization . You are about to reach the first normal form.

However, these properties should be similar in nature (i.e. all phone numbers or addresses, etc.). Do not fall to the dark side (otherwise the anti-template of the object-attribute-value ), and you will be tempted to throw all the properties into the same table. If you can identify multiple attribute types, save each type in a separate table.

Answer 2

If these are all lines of a fixed length (as in the question), you can do the work quite simply (at least in relation to other solutions):

 select substring(col, 1+13*(n-1), 12) as val from t join (select 1 as n union all select union all select 3 ) n on len(t.col) <= 13*nn 

This is a useful hack if all records are the same size (not so easy if they have different sizes). However, think about a data structure, since a comma-separated list (or comma-separated list) is not a very good data structure.

Answer 3

If I were you, I would create a simple function that separates the values ​​separated by the ';' eg:

 IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'fn_Split_List') AND xtype IN (N'FN', N'IF', N'TF')) BEGIN DROP FUNCTION [dbo].[fn_Split_List] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fn_Split_List](@List NVARCHAR(512)) RETURNS @ResultRowset TABLE ( [Value] NVARCHAR(128) PRIMARY KEY) AS BEGIN DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@List, ';', ']]></r><r><![CDATA[') + ']]></r>' INSERT INTO @ResultRowset ([Value]) SELECT DISTINCT RTRIM(LTRIM(Tbl.Col.value('.', 'NVARCHAR(128)'))) FROM @xml.nodes('//r') Tbl(Col) RETURN END GO 

What is simply called like this:

 SET NOCOUNT ON GO DECLARE @RawData TABLE( [Value] NVARCHAR(256)) INSERT INTO @RawData ([Value] ) VALUES ('1111111;22222222') ,('3333333;113113131') ,('776767676') ,('89332131;313131312;54545353') SELECT SL.[Value] FROM @RawData AS RD CROSS APPLY [fn_Split_List] ([Value]) as SL SET NOCOUNT OFF GO 

The result is as follows:

 Value 1111111 22222222 113113131 3333333 776767676 313131312 54545353 89332131 

In any case, the logic in the function is not complicated, so you can easily place it anywhere.

Note. There is no limit on how many values ​​you would share with ;; but the function that you can install on NVARCHAR (MAX) has a length limit.

EDIT:

As I can see, in your example there are several lines that make the function return empty lines. For instance:

 number;number; 

will return:

 number number '' (empty string) 

To clear them, simply add the following where expression to the above expression:

 SELECT SL.[Value] FROM @RawData AS RD CROSS APPLY [fn_Split_List] ([Value]) as SL WHERE LEN(SL.[Value]) > 0