In my environment I have a few tables with thousands of rows that have URLs in particular columns. We are going through some changes in the environment and need to be able to parse out the data (base URL, file name, query parameters) from the specific columns then move the parsed data to another database design. Moving the data is no problem. I plan on using SQL Server 2005 Integration Services and I have a good sense of how to use that tool. By the way, all of the URLs are in varchar fields if that makes any difference. Can you provide any recommendations on how to parse that data?
Although there are probably a few different ways to approach this task, let's break it apart and see how we can code each one of these scenarios with T-SQL commands in SQL Server 2005. I assume you will be parsing the data from a single column to three new columns in a new database\table design. Since you are familiar with SQL Server 2005 Integration Services, I will not dig into those details and just focus on how to capture the data that you need. So let's take a look at each scenario and give an explanation on the T-SQL coding in SQL Server 2005.