Someone at my job needed to grab a filepath, take out the filename and then split all the pieces into their own columns which were separated by either a dot or an underscore. If you had the following table

Then this was the required output.

I thought that this was an interesting exercise, the person who asked me about this wrote some procedures and user defined functions but it was too slow because he was using a lot of charindex and substring functions and storing results in intermediate variables, then continued parsing until the end of the string.

In order to attack this problem you have to divide and conquer. Here are the four steps that are needed

1 Take the dot out

This is pretty easy, all you have to do is use the replace function and replace '.' with '_'

Result
-------------------------------------------------
Y:\d\gfd\gd\gd_Data\bla_2010_10_14_Pre_Calc_BAK

2 Get just the filename

To get just the filename, you use the reverse function, then you look for the position of the first backslash, you will then use the right function and use the position of the first backslash as the number of characters to keep. You add -1 to the length because you do not want the backslash included.

Result
-------------------------------------------------
bla_2010_10_14_Pre_Calc.BAK

2A combine 1 and 2

This is just a combination of 1 and 2, take the dot out and get just the file name

Result
--------------------------------------
bla_2010_10_14_Pre_Calc_BAK

3 Split the string

To split the string we can use the built in table of numbers master..spt_values. Running the code below

This is the output

We are almost there, instead of just one string we want to grab the whole table. We do a cross join with the numbers table and also use the ROW_NUMBER function to assign a column number which we will use as the column name later
Run this to see what we have so far

Here is the output

The missing piece is transposing the columns, this is easily accomplished by using the PIVOT operator. As you can see we use the ROW_NUMBER function to create a number for us, this number is then used as a column name by the Pivot operator. Here is the final query which produced the desired output

The query produces the desired output.

Conclusion

As you can see, if you apply small steps and divide the problem, you can easily solve it even if it looks very difficult at first glance. Remember most likely you can already use something which is built into the product, try not to reinvent the wheel.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum