Tag Archives: SQL

[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