Sql-server – Splitting different parts of a string

sql serverstring

I have an SQL Server database where there is a cell with a delimiter (\) separated string. An example of this string would be:

category_path
=============
RootCategory\Middle Category\Child Category\Child of child category
RootCategory\Middle Category\Other Child Category\

There are a lot of category paths like this. I would like to parse all of them into an other table:

category_name            parent_path
====================================
RootCategory             null
Middle Category          RootCategory
Child Category           RootCategory\Middle Category
Child of child category  RootCategory\Middle Category\Child Category
Other Child Category     RootCategory\Middle Category

I have left out the duplicates here, but the result could contain duplicates (I will need to cursor over the rows of this table later, and I can call distinct there).

There are a lot of examples around the net where a string is simply splitted. (Here for example) I was thinking about writing a query where I split the string, cursor over the results and accumlate the parent_path in each step, but that seems to be very suboptimal.

Is there a solution where I wouldn't have to declare so many cursors for a simple string?

Best Answer

Splitting a string with a recursive CTE is usually a bad choice but this is a case where I at least would consider a solution using that.

If you used a faster string split technique you would have to preserve the order of items and rebuild parent_category using for xml. That might still be faster than using a recursive CTE but here is the recursive stuff anyway.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  category_path nvarchar(max)
);

insert into YourTable values
('RootCategory\Middle Category\Child Category\Child of child category'),
('RootCategory\Middle Category\Other Child Category\');

Query 1:

with C as
(
  select left(category_path, charindex('\', category_path+'\') - 1) as category_name,
         stuff(category_path, 1, charindex('\', category_path+'\'), '') as category_path,
         cast(null as nvarchar(max)) as parent_path
  from YourTable
  union all
  select left(category_path, charindex('\', category_path+'\') - 1),
         stuff(category_path, 1, charindex('\', category_path+'\'), ''),
         coalesce(parent_path+'\', '')+category_name
  from C
  where category_path <> ''
)
select distinct
       category_name,
       parent_path
from C;

Results:

|           CATEGORY_NAME |                                 PARENT_PATH |
-------------------------------------------------------------------------
|          Child Category |                RootCategory\Middle Category |
| Child of child category | RootCategory\Middle Category\Child Category |
|         Middle Category |                                RootCategory |
|    Other Child Category |                RootCategory\Middle Category |
|            RootCategory |                                      (null) |

Related Solutions

Sql-server – SSIS how to parse column with erratic data

For something like this the optimal solution of course is to control your input. That said, the reality is you have to parse the supplied input.

For something as complex as your parsing, I'd skip the Derived Column transformation and go straight for a Script transformation. I select my source column, Input and create three output columns: number, trash and Interval. number and Interval will hold the parsed values while trash will only be populated when the script can't make heads or tails from the input.

I use two member variables, numbersRegex and periodDomain. periodDomain is just a list with the acceptable values. For string comparisons, I force everything to lowercase and hope for English. numbersRegex is a regular expression that is used to identify digits in a string.

For every row that comes in, the script will split the Input value based on whitespace. For each of those tokens, I test whether the token has a digit in it. If it does, we'll call the GetBiggestNumber method. Otherwise, we'll call the ValidatePeriodDomain Once all the tokens have been processed, then it's important to make certain both values have been set.

GetBiggestNumber attempts to look at all the groupings of number and find the largest set.

ValidatePeriodDomain attempts to compare the current value to a known list of acceptable values.

using System.Text.RegularExpressions;
using System.Collections.Generic;

/// <summary>
/// The amazing script transformation
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    Regex numbersRegex;
    List<string> periodDomain;

    /// <summary>
    /// Initialize member variables
    /// </summary>
    public override void PreExecute()
    {
        base.PreExecute();
        // match consecutive digits
        this.numbersRegex = new Regex(@"\d+", RegexOptions.Compiled );
        this.periodDomain = new List<string>(){ "year", "month" };
    }

    /// <summary>
    /// Parse the incoming data
    /// </summary>
    /// <param name="Row">The row that is currently passing through the component</param>
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        string[] parts = Row.Input.Split();
        string period = string.Empty;
        int? foo = null;

        foreach (string token in parts)
        {
            // try to do something with it
            // If the token has a digit in it, then we'll extract the largest value
            // if no digits, then the first token matching our domain is preserved
            if (this.numbersRegex.IsMatch(token))
            {
                foo = GetBiggestNumber(token);
            }
            else
            {
                if (ValidatePeriodDomain(token))
                {
                    period = token;
                }
            }
        }

        // at this point, we've processed the input data
        // If the local variables are in their initial states, then we didn't find
        // anything of note and need to populate the Row.Junk column
        // Why local variables, because can't read from Row.column
        if (period == string.Empty || (foo == null))
        {
            Row.trash = Row.Input;
        }
                    else
                    {
            Row.number = foo.Value;
            Row.Interval = period;
                    }
    }

    private bool ValidatePeriodDomain(string token)
    {
        return (this.periodDomain.Contains(token.ToLower()));
    }

    private int? GetBiggestNumber(string token)
    {
        int? bigOne = null;
        int? current = null;
        // Get all the groups of numbers and compare them
        foreach (Match item in this.numbersRegex.Matches(token))
        {
            current = int.Parse(item.Value);
            if (!bigOne.HasValue)
            {
                bigOne = current;
            }

            if (current.Value > bigOne.Value)
            {
                bigOne = current;
            }
        }

        return bigOne;
    }
}

Using the above script, you can see how it slices and dices the Input data. I made a minor change between the code that generated the below screenshot and what's posted. I observed that the input value 9000 was assigned to Row.number but as that Row never had an Interval assigned, I deferred the actual Row population to the end of the script (it was in the

dataflow

Sql-server – the best approach to parsing a large amount of strings

400 million names is a lot. Am I in there? ;-)

My gut level feeling says that using substring isn't going to be terribly much slower than coding up something via the CLR. I'm a SQL guy, I've done a fair amount of simple parsing in the past (2000 or 2005), and I was involved in what was going to be a very complicated parsing scheme (addresses, world-wide) written in c and called via an xproc until we found that out prototype "native" code wasn't any faster than the same thing written with tsql functions.

If you want to use a language other than tsql, I'd suggest writting a CLR in c# or vb.net. For simple things, it's not hard to code in CLR. I went from newb to having a couple of working directory and file utilities in less than one morning. There are plenty of examples of simple clr procedures on the net. and you wont have to learn anything (or install visual studio) to write it in tsql

No matter what, you will have to go through the table at least once. If you export, then parse and then put back what is not a small amount of data, that's a lot of time. Can you guarantee that your source isn't going to change in the mean time?

Here's the thing that always seems to sneak up on everyone: What happens with the parsed data? Where does it wind up? Do you intend to update the row, perhaps there are lastname and firstname columns that you don't show in your example?

If you do, and those columns are currently null or have zero length stings in them, you may find that the update statement performance is very bad because sql may have to split pages to store the lastname.

In other words, your performance problem isn't parsing, it is storing the parsed data. Often, this is worse than inserting the data into another table. Also, all of those page splits will fragment your table and cause query performance to drop, which may enrage your dba because s/he will have to run a defrag procedure on the (large) table.

Here's one last thought: Do you really need to store the parsed data? Can you get away with a computed column that calculates the last name on the fly? Those are indexable, with certain conditions, if you need that. Another approach would be a view that exposes the columns of the table as well as your "parsed lastname column".

Best Answer

Related Solutions

Sql-server – SSIS how to parse column with erratic data

Sql-server – the best approach to parsing a large amount of strings

Related Question