Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a question on how to parse out a column and hoping a SSIS/Database guru can help out :)

Here is the problem. I have a column saved as a string/varchar format in SQL Server, and need to parse out data from it. I have Business Intelligence Development Studio(BIDS) and SSIS support. I am familiar with using the tool to use derived columns and splitting the data like that using BIDS.

The problem is the data, and here's an example of it.

Easy to parse with derived columns
24 Year     
2 Month 
3 Month 
2 Month 
8 Year  
7 Year

No clue how to approach to parse
1x Month
3x per Month
1-2 x per month
6 days per month

So what i want to essentially do is, if data is standard I want to split the column into two columns, Number(int) and DayType(string), and if it falls into the 2nd category (erratic data), put it in a 3rd column, Other. Any suggestions on how to approach this problem?

Added Clarification: I want to put the non standard data into another 3rd column, so I can look at the data, and run commands on that particular column, to edit out some of the fluff, then make the data workable in the application.

For example, 3x per month, take out the "x per" and then parse that into the columns Number, and Daytype.

Also where there are cases of 1-2, I would like to take the higher number, and drop the lower number, so for example 4-6 days a week, keep 6, drop "a week" and keep days

share|improve this question

migrated from stackoverflow.com Feb 1 '12 at 15:20

This question came from our site for professional and enthusiast programmers.

    
Expected format is single column with int space string and you'd like to split it out? If it doesn't meet that format, push it into a "junk" column. Do you need to validate the domain for the daytype? –  billinkc Jan 25 '12 at 18:20
    
you should be much more specific about what you want to do here. there are several different types of information in your "erratic column" yet no description of how this data is to be used. your current problem is more of a planning problem that a programming problem. –  Chris Drappier Jan 25 '12 at 19:03
    
@billinkc Validation of the daytype would be nice, to clarify, you mean things like Years, Year, and Months and Month? –  Drake0727 Jan 25 '12 at 19:04
    
Seems an odd call to punt this over to dba.stackexchange after it's been closed out. –  billinkc Feb 1 '12 at 16:23

1 Answer 1

up vote 6 down vote accepted

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

share|improve this answer
    
wow, thank you for the very detailed answer. I agree with controlling the input, but I have to import the data AS IS(did not design the application), so that's why it is jacked up. I will now ingest, and try to get it working. SSIS is very daunting for a guy like me straight out of college, and I appreciate the help. I figured a script would be the way to go, but there's not many examples of SSIS parsing scripts on the web (well at least what I could find). Will update with status later –  Drake0727 Jan 25 '12 at 22:56
    
Ok, finally got it working, thanks for the help, this is indeed a start to help me sort through this messy column! Thanks again! –  Drake0727 Jan 26 '12 at 21:31
    
I was wondering if you can use this component to generate multiple rows? –  user11074 Aug 23 '12 at 15:21
    
Hi, I've found another post that answers my question. –  user11074 Aug 23 '12 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.