MSSQL - Split a field into 3 fields

Question

I have a resultset consisting of 1 column and in this case 2 rows the single column [ProductDescription] is a varchar field that hold 3 pieces of information (I didn't design it) i need to get those three pieces of information out into 3 additional fields using a query

before

/------------------------------\
|ProductDescription            |
|------------------------------|
|DB1 - DB2 - DB3               |
|DataBit1 - DataBit2 - DataBit3|
\------------------------------/

After

/---------------------------------------------------------\
|Field1  |Field2  |Field3  |ProductDescription            |  
|---------------------------------------------------------|  
|DB1     |DB2     |DB3     |DB1 - DB2 - DB3               |  
|DataBit1|DataBit2|DataBit3|DataBit1 - DataBit2 - DataBit3|  
\---------------------------------------------------------/

I have tried using combinations of Substring and charindex but haven't been able to get it quite right, each part of the field could be any length so using hardcoded offsets doesn't work.

Answer 1

This isn't pretty, but it works and it does give you what you are looking for, for your specific case... If you had a variable number of tokens in your ProductDescription, you would probably need to create a stored proc to manage your state while parsing the string, as this would quickly grow unmanageable.

create table #table(productdescription varchar(255))
go
/* Demonstrate it working in a 'pretty' case */
INSERT INTO #TABLE (ProductDescription) values ('abc - def - ghi')
go

/* Demonstrate it working in a 'ugly' case */
insert into #table (ProductDescription) values ('jklsaf -mnoa-psdfaqr')
go

SELECT RTRIM(LTRIM(SUBSTRING(ProductDescription, 0, CHARINDEX('-', ProductDescription)-1))) as Field1,

RTRIM(LTRIM(SUBSTRING(ProductDescription, CHARINDEX('-', ProductDescription)+1, (CHARINDEX('-', ProductDescription, CHARINDEX('-', ProductDescription)+1)) - (CHARINDEX('-', ProductDescription)+1)))) as Field2,

RTRIM(LTRIM(SUBSTRING(ProductDescription, CHARINDEX('-', ProductDescription, CHARINDEX('-', ProductDescription)+1)+1, LEN(ProductDescription)))) as Field3
FROM #table
go

I hope this helps!

Answer 2

Assuming that there are always three pieces of information in your ProductDescription column and that the delimiter is always " - " then the following should do the trick:

SELECT
    SUBSTRING(ProductDescription, 1,
        CHARINDEX(' - ', ProductDescription) - 1
    ) AS Field1,
    SUBSTRING(ProductDescription,
        CHARINDEX(' - ', ProductDescription) + 3,
        CHARINDEX(' - ', ProductDescription,
            CHARINDEX(' - ', ProductDescription) + 3
        ) - (CHARINDEX(' - ', ProductDescription) + 3)
    ) AS Field2,
    SUBSTRING(ProductDescription,
        CHARINDEX(' - ', ProductDescription,
            CHARINDEX(' - ', ProductDescription) + 3) + 3,
        LEN(ProductDescription)
    ) AS Field3,
    ProductDescription
FROM your_table

Answer 3

Thanks luke, that is close but not exact.

/----------------------------------------------------------------------------\
|DB1        |DB2       |DB3            |ProductDescription                   |
|----------------------------------------------------------------------------|
|Loading    |Trailer   |Albert Moving  |Loading - Trailer - Albert Moving    |
|Unloading  |Trailer - |Moving Staffers|Unloading - Trailer - Moving Staffers|
\----------------------------------------------------------------------------/

Answer 4

That did it Doug!!!! Thank you, i would mark yours as the answer but i submitted the question without logging in first now it isn't letting me manage the question.

Answer 5

How about an inline function that takes a string, a delimiter, and what part you want back

 Create Function dbo.GetPart(@InString as varchar(1000)
     , @Part as int
     , @Delim as varchar(10))
    Returns varchar(1000) as 
    Begin
        Declare @CurrentPart int
        Declare @i int 
        Declare @j int
        Declare @Ret varchar(1000)
        Set @Ret = ''
        Set @i = 0
        Set @InString = Replace(@InString, ' ', '')

        Set @CurrentPart = 1
        while (@CurrentPart <= @Part)
        Begin
         Set @j =  charindex(@Delim, @InString, @i + 1 ) 
         if @j = 0 set @j = len(@InString) + 1
         if ((@j - @i) > 0 and @CurrentPart = @Part)
         Begin 
          Set @Ret =  Substring(@InString, @i , @j - @i) 
          If @Ret = '' set @ret = 'Weird'
          break
         End
         Set @i = charindex(@Delim, @InString, @i) + len(@delim)
         Set @CurrentPart = @CurrentPart + 1
        End
        if @Ret = '' Set @Ret = 'inconveivable'
        Return @Ret
    End
    GO 
    Select dbo.GetPart('DB1 - DB2 - DB3',1, '-') as Field1
    ,dbo.GetPart('DB1 - DB2 - DB3',2, '-') as Field2
    , dbo.GetPart('DB1 - DB2 - DB3',3, '-') as Field3

    Select dbo.GetPart('DataBit1 - DataBit2 - DataBit3',1, '-') as Field1
    ,dbo.GetPart('DataBit1 - DataBit2 - DataBit3',2, '-') as Field2
    , dbo.GetPart('DataBit1 - DataBit2 - DataBit3',3, '-') as Field3

Answer 6

Here is another solution. Assumes that there is no period (.) in the Product Description:

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