Asked by:
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 | user1@Company.COM | 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 APPLICABLEY0BBBB | user2@company.COM | GURU | New York | READ | ALL | YES | TO BE CONFIRMED | Jane Smith
Tuesday, June 30, 2020 1:08 PM
All replies
-
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.
Tuesday, June 30, 2020 1:39 PM -
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 columnFor 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.
Tuesday, June 30, 2020 1:49 PM -
User77042963 posted
create table testUser(id int, [Contact Email] varchar(200)) insert into testUser values (1,'Y0XXX | user1@Company.COM | 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 | user2@company.COM | 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
Tuesday, June 30, 2020 2:06 PM -
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.
Tuesday, June 30, 2020 3:30 PM -
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 | user1@Company.COM | 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.
Tuesday, June 30, 2020 5:04 PM -
User-1330468790 posted
Hi mrcb86,
What you could to do is basically three steps:
- select the data from original table
- process them in common table (WITH CTE)
- 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 IDThe 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
Wednesday, July 1, 2020 2:37 AM