locked
Delimt a string RRS feed

  • 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 APPLICABLE

    Y0BBBB | 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 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.

    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:

    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

    Wednesday, July 1, 2020 2:37 AM