MSSQL - Divide a field into 3 fields

Question

I have a result set consisting of 1 column, and in this case 2 rows one column [ProductDescription] is a varchar field that stores 3 parts of information (I did not design it) I need to get these three parts of information in 3 additional fields, using request

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 tried using combinations of Substring and charindex, but could not get it completely correctly, each part of the field can be of any length, so using hardcoded offsets does not work.

Answer 1

This is not very good, but it works, and it gives you what you are looking for, for your specific case ... If you had a variable number of tokens in ProductDescription, you probably need to create a stored proc to control your state when parsing lines, as this will quickly become 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

Hope this helps!

Answer 2

, ProductDescription "-", :

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

, ,

 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 4

luke, , .

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

Answer 5

. , (.):

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

Answer 6

"-" "-" , . 3 CSV ( 1) CSV .

, , , , .

Mac