[Quicktip] Easy split in T-SQL

I found a cool and easy solution for splitting a text value into its components. See post @stackoverflow.com.

SELECT 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),3)) DB1, 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),2)) DB2, 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),1)) DB3, 
    ProductDescription
FROM #TABLE T

The magic is done via the PARSENAME-function.

SMALL ISSUE: It only works for items which have 4 components.

--This will not work!!
SELECT 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),5)) DB1, 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),4)) DB2, 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),3)) DB3, 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),2)) DB4, 
    LTRIM(PARSENAME(REPLACE(ProductDescription,'-','.'),1)) DB5, 
    ProductDescription
FROM #TABLE T
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s