CODEWITHSUNDEEP
sql-serverstringt-sqlsql-server-2017
Greg
Greg

Reputation: 87

Split string of variable length, variable delimiters

Read the question posed here, but mine is a little more complicated.

I have a string that is variable in length, and the delimiter can sometimes be two dashes, or sometimes it can be just one. Let's say in my table the data that I want to break out is stored in a single column like this:

+ -----------------------------------------+
| Category                                 |
+------------------------------------------+
| Zoo - Animals - Lions                    |
| Zoo - Personnel                          |
| Zoo - Operating Costs - Power / Cooling  |
+------------------------------------------+

But I want to output the data string from that single column into three separate columns like this:

+----------+--------------------+-----------------+
| Location | Category           | Sub-Category    |
+----------+--------------------+-----------------+
| Zoo      | Animals            | Lions           |
| Zoo      | Personnel          |                 |
| Zoo      | Operating Costs    | Power / Cooling |
+----------+--------------------+-----------------+

Hoping for some guidance as the samples I've been finding on Google seem to be simpler than this.

Upvotes: 0

Views: 1409

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You've tagged this with [sql-server-2017]. That means, that you can use JSON-support (this was introduced with v2016).

Currently JSON is the best built-in approach for position- and type-safe string splitting:

A mockup, to simulate your issue

DECLARE @mockup TABLE (ID INT IDENTITY, Category VARCHAR(MAX))

INSERT INTO @mockup (Category)
VALUES ('Zoo - Animals - Lions')
      ,('Zoo - Personnel')
      ,('Zoo - Operating Costs - Power / Cooling');

--The query

SELECT t.ID
      ,A.[Location] 
      ,A.Category 
      ,A.subCategory 
FROM @mockup t
CROSS APPLY OPENJSON(CONCAT('[["',REPLACE(t.Category,'-','","'),'"]]')) 
WITH ([Location] VARCHAR(MAX) '$[0]'
     ,Category VARCHAR(MAX) '$[1]'
     ,SubCategory VARCHAR(MAX) '$[2]') A;

The result (might need some TRIM()ing)

ID  Location    Category            subCategory
1   Zoo         Animals             Lions
2   Zoo         Personnel           NULL
3   Zoo         Operating Costs     Power / Cooling

The idea in short:

We use some simple string operations to transform your string into a JSON array:

a b c    => [["a","b","c"]]

Now we can use OPENJSON() together with a WITH-clause to return each fragment by its position with a fixed type.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

You can also use a string splitter. Here is an excellent one that works with your version. DelimitedSplit8K

Now we need some sample data.

declare @Something table
(
    Category varchar(100)
)

insert @Something values
('Zoo - Animals - Lions')
, ('Zoo - Personnel')
, ('Zoo - Operating Costs - Power / Cooling')

Now that we have a function and sample data the code for this is quite nice and tidy.

select s.Category
    , Location = max(case when x.ItemNumber = 1 then Item end)
    , Category = max(case when x.ItemNumber = 2 then Item end)
    , SubCategory = max(case when x.ItemNumber = 3 then Item end)
from @Something s
cross apply dbo.DelimitedSplit8K(s.Category, '-') x
group by s.Category

And this will return:

Category                                |Location|Category       |SubCategory
Zoo - Animals - Lions                   |Zoo     |Animals        |Lions
Zoo - Operating Costs - Power / Cooling |Zoo     |Operating Costs|Power / Cooling
Zoo - Personnel                         |Zoo     |Personnel      |NULL

Upvotes: 2

GMB
GMB

Reputation: 222482

Here is a solution that uses solely string functions:

select 
    left(
        category, 
        charindex('-', category) - 2
    ) location,
    substring(
        category, 
        charindex('-', category) + 2, 
        len(category) - charindex('-', category, charindex('-', category) + 1)
    ) category,
    case when charindex('-', category, charindex('-', category) + 1) > 0 
        then right(category, charindex('-', reverse(category)) - 2) 
    end sub_category
from t

Demo on DB Fiddle:

location | category         | sub_category   
:------- | :--------------- | :--------------
Zoo      | Animal           | Lions          
Zoo      | Personnel        | null           
Zoo      | Operating Costs  | Power / Cooling

Upvotes: 2

squillman
squillman

Reputation: 13641

Bit of hack, but it works:

DECLARE @t TABLE (Category VARCHAR(255))

INSERT @t (Category)
VALUES ('Zoo - Animals - Lions'),('Zoo - Personnel'),('Zoo - Operating Costs - Power / Cooling')

;WITH split_vals AS (
    SELECT Category AS Cat,TRIM(Value) AS Value,ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Category) AS RowNum
    FROM @t
    CROSS APPLY STRING_SPLIT(Category,'-')
), cols AS (
    SELECT
        Cat,
        CASE WHEN RowNum = 1 THEN Value END AS Location,
        CASE WHEN RowNum = 2 THEN Value END AS Category,
        CASE WHEN RowNum = 3 THEN Value END AS [Sub-Category]
    FROM split_vals
)
SELECT STRING_AGG(Location, '') AS Location,
       STRING_AGG(Category, '') AS Category,
       STRING_AGG([Sub-Category], '') AS [Sub-Category]
FROM cols
GROUP BY Cat;

Upvotes: 0

Related Questions