A colleague of mine received an Excel file that he needed to load into a table for future lookup values. There were some crazy columns containing multiple values inside one cell, which he needed to split up into multiple columns. Seems pretty straight forward so far right? And a pretty common occurrence when working on Business Intelligence solutions for clients, because the data can come from many different sources and could include Excel files.

After importing the spreadsheet into SQL Server, one could use a similar approach that I demonstrate below to separate these values. The below script will create a temporary table that holds values which could then be used as an example of how to use the PATINDEX function to split out some values.

As an example, let’s say we have a column that has a part number and the part description concatenated and we have values in the spreadsheet such as the one shown in figure 1 below.

Figure 1: Excel Spreadsheet Values For MyParts

One way of doing this (NOT THE ONLY WAY), is to use PATINDEX to determine where the parenthesis are. Script 1 below has a quick example on how to do this.

Script 1: Sample Script

CREATE TABLE #MyParts(PartDesc VARCHAR(100))

INSERT INTO #MyParts SELECT '(12345) – My Wicked Crazy Part'

INSERT INTO #MyParts SELECT '(3333333) – Darth Vader Helmet'

INSERT INTO #MyParts SELECT '(44444444) – Luke Skywalker Belt'

SELECT

      PartDesc

      , PATINDEX('%(%', PartDesc) AS OpeningParenStart

      , PATINDEX('%)%', PartDesc) AS ClosingParenStart

      , PATINDEX('%–%',PartDesc) AS Hyphen

      , PATINDEX('% – %',PartDesc) + 3 AS LastSpace

      , REPLACE(REPLACE(SUBSTRING(PartDesc,PATINDEX('%(%', PartDesc),PATINDEX('%)%', PartDesc)), '(',''),')','') AS InnerValue

      , SUBSTRING(PartDesc, PATINDEX('% – %',PartDesc) + 3, LEN(PartDesc)) AS OuterValue

FROM

      #MyParts

After running this query, you will see results as shown in Figure 2.

Figure 2: Results from Script 1

Note: This is a down and dirty way of doing this and is by no means represents the “only” way this could be done. It was used mainly to quickly split the values.

If you would like the script, you can download it here.

Until next time, “keep your ear to the grindstone” – Good Will Hunting

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter