How to split string into columns for a view? [duplicate]

Question

I have a column (full_location_id) in a table that contains a string is delimited by '-' which I need to split up into 4 columns in a view (Test_SplitColumn). Not every record in full_location_id contains the same length of ids. Some may have ids such as A1-BF-35-B1 while others may simply have AR-B3. I'm not sure about the best way of doing so. I am able to retrieve the first column but so far the not the others.

CREATE VIEW [dbo].[Test_SplitColumn]
AS
select p.name, location.aisle_id, location.full_location_id, SUBSTRING(location.full_location_id, 0,charindex('-', location.full_location_id )) as Aisle,
SUBSTRING(location.full_location_id, charindex('-', location.full_location_id ) + 1, charindex('-', location.full_location_id, LEN(SUBSTRING(location.full_location_id, 0,charindex('-', location.full_location_id ))) )) as ShelvingUnit
from location 
inner join product p on p.id = location.product_id
GO

Any help or guidance would be appreciated.

Answer 1

Here is a quick, easy way:

DECLARE @T TABLE(full_location_id varchar(100));

INSERT INTO @T 
VALUES  ('A1-BF-35-B1'),
        ('AR-B3');

WITH CTE AS
(
    SELECT  full_location_id,
            LEN(full_location_id)-LEN(REPLACE(full_location_id,'-','')) N
    FROM @T
)
SELECT  full_location_id,
        PARSENAME(REPLACE(full_location_id,'-','.'),N+1),
        PARSENAME(REPLACE(full_location_id,'-','.'),N),
        PARSENAME(REPLACE(full_location_id,'-','.'),N-1),
        PARSENAME(REPLACE(full_location_id,'-','.'),N-2)
FROM CTE

Results:

╔══════════════════╦══════╦══════╦══════╦══════╗
║ full_location_id ║ Col1 ║ Col2 ║ Col3 ║ Col4 ║
╠══════════════════╬══════╬══════╬══════╬══════╣
║ A1-BF-35-B1      ║ A1   ║ BF   ║ 35   ║ B1   ║
║ AR-B3            ║ AR   ║ B3   ║ NULL ║ NULL ║
╚══════════════════╩══════╩══════╩══════╩══════╝

And here is an sqlfiddle with a demo.

Answer 2

This is a failure of your model. Instead of storing the locations as a delimited string it's probably a good idea to make a 1-n table to store the locations in instead. And in fact the correct 'answer' to your question is probably "redesign this part of the database!"

However, to do what you want you can do stuff like this:

USE tempdb
GO

/* udfSplit (A Fast String Splitter) **************************************************************
 *
 * Uses a number table to *very* quickly split the text (@text). Splits on the delimiter (@d)
 * Returns Table of ( [RowID], [SplitText] ). Inlineable for CROSS APPLY etc.
 *
 * Charlie
 *
 *************************************************************************************************/
CREATE FUNCTION [dbo].[udfSplit] (@text NVARCHAR(4000), @d NVARCHAR(50))
RETURNS TABLE AS RETURN (
WITH numbers(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY a.[n])
  FROM
             ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS  a ([n])
  CROSS JOIN ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS  b ([n])
  CROSS JOIN ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS  c ([n])
  CROSS JOIN ( VALUES (0), (1), (2), (3), (4)) AS  d ([n])
  )
SELECT
      [RowID] = ROW_NUMBER() OVER ( ORDER BY [n] ASC )
    , [SplitText] = SUBSTRING(
        @d + @text + @d
        , [n] + LEN(@d)
        , CHARINDEX(@d, @d + @text + @d, [n] + LEN(@d)) - [n] - LEN(@d)
        )
FROM numbers AS n
WHERE [n] <= LEN(@d + @text + @d) - LEN(@d)
  AND SUBSTRING(@d + @text + @d, [n], LEN(@d)) = @d
)
GO

IF OBJECT_ID('tempdb..#sample') IS NOT NULL DROP TABLE #sample
GO

CREATE TABLE #sample (
      name VARCHAR(255)
    , locations VARCHAR(MAX)
    )

INSERT #sample (name, locations)    
VALUES ('a', 'ab-cd')
     , ('b', 'ab-cd-ef')
     , ('c', 'gh')
     , ('d', NULL)

; WITH SPLIT AS (
    SELECT [name], l.*
    FROM #sample AS s
    OUTER APPLY dbo.[udfSplit](s.locations,'-') AS l
    )
SELECT
      s.name
    , MAX(CASE WHEN s.rowId = 1 THEN s.SplitText ELSE '' END) AS a
    , MAX(CASE WHEN s.rowId = 2 THEN s.SplitText ELSE '' END) AS b
    , MAX(CASE WHEN s.rowId = 3 THEN s.SplitText ELSE '' END) AS c
    , MAX(CASE WHEN s.rowId = 4 THEN s.SplitText ELSE '' END) AS d
FROM
    SPLIT AS s
GROUP BY
    s.name

This probably looks super complicated. The function udfSplit is a very fast string splitter -- it turns your delimited string into a table returning postion (1-4) and the split string. Unless you really want to get into it just don't worry how it works. If you do want to learn about splitting strings in the db (and in general why that's a bad plan) -- then read here:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

The rest of the code makes up a sample table and then does a select on it to get the output you wanted:

(4 row(s) affected)
name                 a     b     c     d
-------------------- ----- ----- ----- -----
a                    ab    cd          
b                    ab    cd    ef    
c                    gh                
d                                      

The MAX(CASE....) Expressions are a pivoting trick back in sql server 2000 land. I never got the hang of the PIVOT operator.

SQL Fiddle: http://sqlfiddle.com/#!3/80f74/1