How to select a row of data with multiple delimiters

Question

I have a table in SQL Server 2012 that has a column that contains following data:

EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS

Semicolons separate the employers, up to 5, and commas separate the EmployerName and Employer address.

I need to select the row and display as follows:

EMPNAME1 EMPADDRESS1 EMPNAME2 EMPADDRESS2 EMPNAME3 EMPADDRESS3.....

You should make more clear what you are asking and show what you have tried so far. — Jan, Oct 31 '17 at 17:06

score 0 · Answer 1 · answered Oct 31 '17 at 17:22

0

Basically I would write a specific splitstring function that suites your need and use it in a select on that source table. Have a look at CHARINDEX() and SUBSTRING(). Here is an quickly grabbed example that could make things clear: T-SQL split string and further information on how to write a tsql function: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql

No code in question, no code in answer, hope it helps =)

answered Oct 31 '17 at 17:22

Jan

333
2
15

Good afternoon Jan. I didn't realise it at first, but I feel that my answer is an extension to your answer. Would you like me to add my code to your answer? Let me know – WonderWorker Jan 15 '18 at 14:25
No way! Your answer took much more effort than mine, so you should "harvest the laurels" if there ever will be any. =) – Jan Jan 16 '18 at 14:36

WonderWorker · Answer 2 · 2018-01-15T14:22:18.913

You can create a Split function with the following code:

CREATE FUNCTION dbo.split (    

    --DECLARE
          @Input NVARCHAR(MAX),
          @Character CHAR(1)

) RETURNS @Output TABLE (Item NVARCHAR(1000))
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1

      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
          BEGIN
                SET @Input = @Input + @Character

          END

      WHILE CHARINDEX(@Character, @Input) > 0
          BEGIN
                SET @EndIndex = CHARINDEX(@Character, @Input)

                INSERT INTO @Output(Item)
                SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

                SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

          END

      RETURN

END
GO

To make the function available to the whole server, run the above code.

Once created, the function can be referenced in a query in a similar way to how a table is referenced, as follows:

SELECT 
    CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY Item)) AS EmployeeId, 
    SUBSTRING(Item, 0, CHARINDEX (',', Item)) AS EmployeeName,
    SUBSTRING(Item, CHARINDEX (',', Item) + 1,LEN(Item)) AS EmployeeAddress
FROM 
    dbo.split('EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS', ';') AS Employee

The above query will display your data in a neat table.

Using this as a base, I created the following procedure, which eccepts a single NVARCHAR input parameter, and displays the columns in the way that you requested:

CREATE PROCEDURE Employee(

    --DECLARE
            @Input NVARCHAR(MAX) = 'EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS'

) AS
BEGIN

    SELECT 
        CONVERT(NVARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY Item)) AS EmployeeId, 
        SUBSTRING(Item, 0, CHARINDEX (',', Item)) AS EmployeeName,
        SUBSTRING(Item, CHARINDEX (',', Item) + 1,LEN(Item)) AS EmployeeAddress
    INTO #Employees
    FROM 
        dbo.split(@Input, ';') AS Employee


    SELECT
        (SELECT TOP 1 EmployeeName FROM #Employees WHERE EmployeeId = 1) AS EmpName1,
        (SELECT TOP 1 EmployeeAddress FROM #Employees WHERE EmployeeId = 1) AS EmpAddress1,
        (SELECT TOP 1 EmployeeName FROM #Employees WHERE EmployeeId = 2) AS EmpName2,
        (SELECT TOP 1 EmployeeAddress FROM #Employees WHERE EmployeeId = 2) AS EmpAddress2,
        (SELECT TOP 1 EmployeeName FROM #Employees WHERE EmployeeId = 3) AS EmpName3,
        (SELECT TOP 1 EmployeeAddress FROM #Employees WHERE EmployeeId = 3) AS EmpAddress3,
        (SELECT TOP 1 EmployeeName FROM #Employees WHERE EmployeeId = 4) AS EmpName4,
        (SELECT TOP 1 EmployeeAddress FROM #Employees WHERE EmployeeId = 4) AS EmpAddress4,
        (SELECT TOP 1 EmployeeName FROM #Employees WHERE EmployeeId = 5) AS EmpName5,
        (SELECT TOP 1 EmployeeAddress FROM #Employees WHERE EmployeeId = 5) AS EmpAddress5

    DROP TABLE #Employees

END

The way the procedure works is as follows:

Save the table to a temporary table named #Employees.
Select the top 5 Employees as a single row.
Drop the temporary table.

Use the procedure in the following way:

exec Employee 'EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS;EMPLOYER-NAME,EMPADDRESS'

How to select a row of data with multiple delimiters

2 Answers2