Delimt a string

Question

User1603547060 posted

Hi, I have the following code:

SELECT [User Name

,[Full Name]

,CASE WHEN [State] = 1 THEN 'DISABLED' when [State] = 0 then 'ENABLED' end as [State]

,CASE WHEN [License Type] = 4 THEN 'EXTERNAL USER' WHEN [License Type] = 0 THEN 'FULL USER' when [License Type] = 1 THEN 'LIMITED USER' end as [License Type]

,[Contact Email]

FROM [LIVEBC].[dbo].[User]

ORDER BY [Full Name]

The data in column Contact Email is always split by eight pipes | - therefore 9 "columns" - the date is variable - so the code needs to be dynamic

Can anyone help me with amending the code to delimit Contact Email Column into 9 Columns?

Examples of the data in the string in column Contact Email:

Y0XXX | [e-mail] | SPECIALIST | London | SUPER | ALL | NO | PC | Joe Bloggs

Y0YYY | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE

Y0AAA | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE

Y0BBBB | [e-mail] |  GURU | New York | READ | ALL | YES | TO BE CONFIRMED | Jane Smith

Answer 1

User475983607 posted

For problem has been solved for quite some time that a basic Google search will turn up.

SQL 2016 has STRING_SPLIT.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

OR write a function.

https://stackoverflow.com/questions/10914576/t-sql-split-string

IMHO, the best fix is fixing the design and using table columns to hold the delimited fields.

Answer 2

User753101303 posted

Hi,

If using SQL Server 2016 or later you could consider gradually:
- to convert on the fly this string to JSON and then use https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15
- to store directly those values using JSON to ease handling
- maybe fix the schema as for now it seems there is no real reason for shoe horning all this in a single column

For now my understanding is that you always have 9 columns (not sure what is the "date" you are talking about and that would make this to be "dynamic" whatever you mean).

Edit: or you mean that this column could have multiple lines each with 9 columns??? As pointed already not using the proper design likely just made things harder past the first initial "no table change" benefit.

Answer 3

User77042963 posted

create table testUser(id int,  [Contact Email] varchar(200))
insert into testUser values
(1,'Y0XXX | [e-mail] | SPECIALIST | London | SUPER | ALL | NO | PC | Joe Bloggs'),
(2,'Y0YYY | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE'),
(3,'Y0AAA | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE | NOT APPLICABLE'),
(4,'Y0BBBB | [e-mail] |  GURU | New York | READ | ALL | YES | TO BE CONFIRMED | Jane Smith')
--SQL Server 2016 or up
;with mycte as (
select *,
'{"data":["'+ REPLACE([Contact Email], '|', '","') + '"]}' jsCol
from  testUser)
Select
id
,LVL_1
,LVL_2
,LVL_3
,LVL_4
,LVL_5
,LVL_6
,LVL_7
,LVL_8
,LVL_9
 
FROM mycte
cross apply openjson(jsCol,'$')
WITH (
LVL_1 NVARCHAR(100) '$.data[0]',
LVL_2 NVARCHAR(100) '$.data[1]',
LVL_3 NVARCHAR(100) '$.data[2]',
LVL_4 NVARCHAR(100) '$.data[3]',
LVL_5 NVARCHAR(100) '$.data[4]',
LVL_6 NVARCHAR(100) '$.data[5]',
LVL_7 NVARCHAR(100) '$.data[6]',
LVL_8 NVARCHAR(100) '$.data[7]',
LVL_9 NVARCHAR(100) '$.data[8]' 
 
) t


drop table testUser

Answer 4

User1603547060 posted

Thanks, this does look like a step in the right direction, but by making this temp table I need to hard code this data? This is data is over 2000 rows and is ever changing. I want to make it dynamic to delimit so that it can be refreshed at any time in excel.

Answer 5

User753101303 posted

You meant testUser? This is just to show a ready to run sample but you could apply the same approach on your own data or create a view maybe to simplify Excel side query

It could be maybe a bit simpler with https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

For example :

SELECT
JSON_VALUE(value,'$[0]') AS col1,
JSON_VALUE(value,'$[1]') AS col2,
JSON_VALUE(value,'$[2]') AS col3,
JSON_VALUE(value,'$[3]'),
JSON_VALUE(value,'$[4]'),
JSON_VALUE(value,'$[5]'),
JSON_VALUE(value,'$[6]'),
JSON_VALUE(value,'$[7]'),
JSON_VALUE(value,'$[8]')
FROM
(	
	SELECT '["'+ REPLACE(value, '|', '","') + '"]' AS value FROM
	(
		SELECT 'Y0XXX | [e-mail] | SPECIALIST | London | SUPER | ALL | NO | PC | Joe Bloggs' AS value
	) AS YourActualTableInstead
) AS ConvertedToJson

The inner query is just to provide data without creating a table and could be replaced by an actual table name.

Edit 2: also you could use TRIM or replace ' | ' depending on how you want to handle spaces around your data.

Answer 6

User-1330468790 posted

Hi mrcb86,

What you could to do is basically three steps:

1. select the data from original table
2. process them in common table (WITH CTE)
3. decide to fetch which column data you want. 

Above members have provide you with some approaches like STRING_SPLIT, function and even an example about JSON.

 

If you just have over 2000 rows, the performance of these methods would not be very different. However, if you have data within over 20000 rows or even more, I recommend you using STRING_SPLIT as the speed would be the fastest. Generally, the performance rank is STRING_SPLIT > JSON > FUNCTION > XML. You could do some tests with a larger scale of the data.

In your case, you could use STRING_SPLIT + PIVOT to split the column [Contact Email] into different columns.

WITH C AS(
SELECT ID,
       [User Name],
       [Full Name],
       VALUE,
       ROW_NUMBER() OVER(PARTITION BY [Contact Email] ORDER BY (SELECT NULL)) as RN
FROM LIVEBC A
CROSS APPLY STRING_SPLIT([Contact Email], '|') AS B
) 
SELECT
       ID,
       [User Name],
       [Full Name],
       [1] AS Column1,
       [2] AS Column2,
       [3] AS Column3,
       [4] AS Column4,
       [5] AS Column5,
       [6] AS Column6,
       [7] AS Column7,
       [8] AS Column8
FROM C
PIVOT(
    MAX(Value)
    FOR RN IN([1],[2],[3],[4],[5],[6],[7],[8])  
) as PVT
ORDER BY ID

The table data:

The result data table:

The only drawback of the STRING_SPLIT way is that we can not guarantee the order of the output. Therefore you have to add an "Order By" in your select statement.

About the drawback of STRING_SPLIT illustrated in document (STRING_SPLIT (Transact-SQL)):   

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).

 

Hope this can help you.

Best regards,

Sean