SQL 2005 Format Question

Question

Hi Guys I have a fully functioning query, but need to do a little formatting. One of my fields is called a route name. An example of the data in that field is "PRN L5 L7 S LAM C"

Now what I need to do is firstly remove the PRN, secondly split the route into seperate columns, so column 1 would have L5, column 2 would have L7 ect....

Now, the route operations (L5, L7, LAM) would have either 1,2 or 3 characters in no paticular order. Any body got any ideas?

Answer 1

I'd look at creating a CLR function that uses regular expressions.

See this link: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

From the article:

[SqlFunction]
public static SqlChars RegexGroup( 
    SqlChars input, SqlString pattern, SqlString name )
{
    Regex regex = new Regex( pattern.Value, Options );
    Match match = regex.Match( new string( input.Value ) );
    return match.Success ?
        new SqlChars( match.Groups[name.Value].Value ) : SqlChars.Null;
}

Answer 2

You could also create a split function http://www.kodyaz.com/articles/sql-server-t-sql-split-function.aspx