Deleted

  • Deleted.

  • Have a look into the appropriately called function STRING_SPLIT, or, if you need to keep detail on the ordinal position, DelimitedSplit8k. If you have any problems, please do reply with what you tried

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Deleted.

  • chocthree - Monday, August 13, 2018 8:17 AM

    I tried the STRING_SPLIT function but it came back as an Invalid Object name. I understand I need to include the function somehow. My access on the SQL Server is restricted to minimal read only hence, I guess, why I couldn't register the function to use. I understand I should see a folder named 'Programmability' which houses all the functions I call use. On this particular server I cannot see that folder.
    I ended up using Power Query to achieve my aim.

    You'll need to CROSS APPLY the STRING_SPLIT function as it is a table valued function, otherwise it will be parsed as a scalar object.
    😎

    There are two variants of the DelimitedSplit8k function for SQL Server 2008 and earlier and for SQL Server 2012 and later respectfully which are alternatives to the STRING_SPLIT, most useful when you need to preserve the order of the elements, DelimitedSplit8k and DelimitedSplit8k_LEAD

  • chocthree - Monday, August 13, 2018 8:17 AM

    I tried the STRING_SPLIT function but it came back as an Invalid Object name. I understand I need to include the function somehow. My access on the SQL Server is restricted to minimal read only hence, I guess, why I couldn't register the function to use. I understand I should see a folder named 'Programmability' which houses all the functions I call use. On this particular server I cannot see that folder.
    I ended up using Power Query to achieve my aim.

    Otherwise, if you did use CROSS APPLY, and got this error, it means that you're not using the version of SQL Server you think you are. STRING_SPLIT was introduced in SQL Server 2016, so it is on 2017 (which is the forum you've psoted in), however, if you're using a version prior to that, then you'll get an error.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Thom A - Monday, August 13, 2018 9:20 AM

    chocthree - Monday, August 13, 2018 8:17 AM

    I tried the STRING_SPLIT function but it came back as an Invalid Object name. I understand I need to include the function somehow. My access on the SQL Server is restricted to minimal read only hence, I guess, why I couldn't register the function to use. I understand I should see a folder named 'Programmability' which houses all the functions I call use. On this particular server I cannot see that folder.
    I ended up using Power Query to achieve my aim.

    Otherwise, if you did use CROSS APPLY, and got this error, it means that you're not using the version of SQL Server you think you are. STRING_SPLIT was introduced in SQL Server 2016, so it is on 2017 (which is the forum you've psoted in), however, if you're using a version prior to that, then you'll get an error.

    You should also get an error if your database is set at a lower compatibility level than 2016 ...

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • STRING_SPLIT won't help in this case as it provides no guarantees of what order the results are in nor any way of detecting the position in the string that each element came from (something of a flaw in the current design). Get a copy of DelimitedSplit8K from this site (assuming your columns are never over 8000 characters) as that provides the requisite capabilities.

  • chocthree - Monday, August 13, 2018 6:30 AM

    Hi,
    I have the following query:
    SELECT
        rc.[EmailAddress] AS [Email Address]
    ,    cd.[Name] AS [Candidates Last : Candidate First]
    ,    CASE
            WHEN CHARINDEX(':', cd.Name) <> 0 THEN
        RIGHT( cd.Name, LEN( RIGHT( cd.Name, LEN( cd.Name ))) - LEN( RIGHT( cd.Name, CHARINDEX( ':', cd.Name ))))
       ELSE
        RIGHT( cd.Name, LEN( CHARINDEX( ' ', REVERSE( cd.Name )) -1 ) )
       END
        AS [Candidate First Name]
    , CASE
       WHEN CHARINDEX(':', cd.Name) <> 0 THEN
        LEFT( cd.Name, LEN( LEFT( cd.Name, CHARINDEX( ':', cd.Name ) -1 )))
       ELSE
        LEFT( cd.Name, LEN( cd.Name ) - CHARINDEX( ' ', REVERSE( cd.Name )))
       END
        AS [Candidate Last Name]
    ,    ce.[CanSid] AS [Candidate Number]
    ,    ck.[UCI] AS [Unique Identifier]
    ,    cd.[DOB] AS [DoB]
    ,    q.[ShortName] AS [Exam]
    ,    q.[Name] AS [Exam Level]
    ,    rig.[KAD] AS [Key Assessment Date]
    ,    CASE
            WHEN LEFT( q.[ShortName], 2 ) = 'CB' THEN 'CB'
            WHEN LEFT( q.[ShortName], 2 ) = 'PB' THEN 'PB'
            ELSE 'Other'
        END
        AS [CB or PB]
    ,    ct.[CnuId] AS [Centre No]
    ,    ct.[Name] AS [Centre Name]
    ,    ct.[Active] AS [Centre Status]
    ,    ct.[EsolTown] AS [Country Exam taken in]
    ,    NULL AS [Centre Region]
    ,    NULL AS [Nationality]
    ,    cd.[Sex] AS [Gender]
    ,    NULL AS [Special Arrangement Flag Y/N]
    ,    ce.[ResultInfo] AS [ResultInfo]
    FROM [dbo].[CandidateEntries] ce
    JOIN [dbo].[Candidates] cd ON ce.[CanSid] = cd.[CanSid]
    JOIN [dbo].[CandidateKeys] ck ON ce.[CanSid] = ck.[CanSid]
    JOIN [dbo].[RegisteredCandidate] rc ON ck.[UCI] = rc.[UCI]
    JOIN [dbo].[Centres] ct ON ce.[CnuId] = ct.[CnuId]
    JOIN [dbo].[Qualifications] q ON ce.[QuaSid] = q.[QuaSid]
    JOIN [dbo].[SessionQuals] sq ON ce.SesSid = sq.SesSid
    JOIN [dbo].[ResultIssueGroups] rig ON sq.RigSid = rig.RigSid

    The ce.ResultsInfo value will be as follows:

    As you can see it is | delimited. From within the SQL query I need a way to split the values (whether they are NULL or not) into separate columns for the row. The number of | should be 18. So that will be 19 columns to be returned. I would like to be able to Alias each column too (A, B, C, etc..) will be fine but if this adds to complexity then I can just name them on the export of the SQL query.

    Please can someone nudge me in the right direction on how to achieve this please?  Preferably dynamically in case there aren't always 18 delimters (|) present.
    Thanks.

    It would look something like the following:
    SELECT
        rc.[EmailAddress] AS [Email Address]
    ,    cd.[Name] AS [Candidates Last : Candidate First]
    ,    CASE
            WHEN CHARINDEX(':', cd.Name) <> 0 THEN
                RIGHT(cd.Name, LEN(RIGHT(cd.Name, LEN(cd.Name))) -
                    LEN(RIGHT(cd.Name, CHARINDEX(':', cd.Name ))))
            ELSE RIGHT(cd.Name, LEN(CHARINDEX(' ', REVERSE(cd.Name)) -1))
        END AS [Candidate First Name]
    ,    CASE
            WHEN CHARINDEX(':', cd.Name) <> 0 THEN
                LEFT(cd.Name, LEN(LEFT(cd.Name, CHARINDEX(':', cd.Name) -1)))
            ELSE LEFT(cd.Name, LEN(cd.Name ) - CHARINDEX(' ', REVERSE(cd.Name)))
        END AS [Candidate Last Name]
    ,    ce.[CanSid] AS [Candidate Number]
    ,    ck.[UCI] AS [Unique Identifier]
    ,    cd.[DOB] AS [DoB]
    ,    q.[ShortName] AS [Exam]
    ,    q.[Name] AS [Exam Level]
    ,    rig.[KAD] AS [Key Assessment Date]
    ,    CASE
            WHEN LEFT(q.[ShortName], 2) IN ('CB', 'PB') THEN LEFT(q.[ShortName], 2)
            ELSE 'Other'
        END AS [CB or PB]
    ,    ct.[CnuId] AS [Centre No]
    ,    ct.[Name] AS [Centre Name]
    ,    ct.[Active] AS [Centre Status]
    ,    ct.[EsolTown] AS [Country Exam taken in]
    ,    NULL AS [Centre Region]
    ,    NULL AS [Nationality]
    ,    cd.[Sex] AS [Gender]
    ,    NULL AS [Special Arrangement Flag Y/N]
    ,    ce.[ResultInfo] AS [ResultInfo]
    ,    SP.[1] AS xxx1
    ,    SP.[2] AS xxx2
    ,    SP.[3] AS xxx3
    ,    SP.[4] AS xxx4
    ,    SP.[5] AS xxx5
    ,    SP.[6] AS xxx6
    ,    SP.[7] AS xxx7
    ,    SP.[8] AS xxx8
    ,    SP.[9] AS xxx9
    ,    SP.[10] AS xxx10
    ,    SP.[11] AS xxx11
    ,    SP.[12] AS xxx12
    ,    SP.[13] AS xxx13
    ,    SP.[14] AS xxx14
    ,    SP.[15] AS xxx15
    ,    SP.[16] AS xxx16
    ,    SP.[17] AS xxx17
    ,    SP.[18] AS xxx18
    ,    SP.[19] AS xxx19
    FROM [dbo].[CandidateEntries] AS ce
    JOIN [dbo].[Candidates] AS cd
        ON ce.[CanSid] = cd.[CanSid]
    JOIN [dbo].[CandidateKeys] AS ck
        ON ce.[CanSid] = ck.[CanSid]
    JOIN [dbo].[RegisteredCandidate] AS rc
        ON ck.[UCI] = rc.[UCI]
    JOIN [dbo].[Centres] AS ct
        ON ce.[CnuId] = ct.[CnuId]
    JOIN [dbo].[Qualifications] AS q
        ON ce.[QuaSid] = q.[QuaSid]
    JOIN [dbo].[SessionQuals] AS sq
        ON ce.SesSid = sq.SesSid
    JOIN [dbo].[ResultIssueGroups] AS rig
        ON sq.RigSid = rig.RigSid
    OUTER APPLY (
        SELECT [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13],
            [14], [15], [16], [17], [18], [19]
        FROM dbo.DelimitedSplit8K_LEAD(ce.ResultInfo, '|')
        PIVOT (MAX(Item) FOR ItemNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13],
            [14], [15], [16], [17], [18], [19])) AS PVT
        ) AS SP

    You'd want to change the output column names at the end of the SELECT to match your needs.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply