Post reply

Splitting a single column into multiple columns

  • March 3, 2015 at 7:14 am

    #298240

    Hi Team,

    I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.

    For Eg:

    Description

    APPEARANCE UNIT

    BDV KV

    DENSITY KG/L

    Can any one suggest with the query to separate these.

    Thanks in advance..

  • March 3, 2015 at 7:45 am

    #1781355

    If there is only ever one mid-string space then the split could be done as follows:

    DECLARE @STR CHAR(40)= 'something unit'

    SELECTSUBSTRING(@Str,1,CHARINDEX(' ',@Str)-1) AS [LeftPart],

    SUBSTRING(@Str,(CHARINDEX(' ',@Str)+1),LEN(@Str)) AS [RightPart]

  • March 17, 2015 at 4:18 am

    #1784435

    Thanks andy...

    We have like after 40 characters the unit of measure is place in the description field. We have more than one space between the description and unit of measure.

  • March 17, 2015 at 4:36 am

    #1784443

    You could replace the multiple spaces with a single space first:

    WHILE PATINDEX('% %',@str)>0-- replace multiple spaces with single spaces

    BEGIN

    SET @STR=REPLACE(@str,' ',' ')

    END

    So the whole thing would read:

    DECLARE @STR CHAR(40)= 'something unit'

    WHILE PATINDEX('% %',@str)>0

    BEGIN

    SET @STR=REPLACE(@str,' ',' ')

    END

    SELECTSUBSTRING(@Str,1,CHARINDEX(' ',@Str)-1) AS [LeftPart],

    SUBSTRING(@Str,(CHARINDEX(' ',@Str)+1),LEN(@Str)) AS [RightPart]

  • March 17, 2015 at 5:34 am

    #1784454

    Quick suggestion, use DelimitedSplit8K[/url]

    😎

  • March 17, 2015 at 6:42 am

    #1784470

    sqlquery29 (3/17/2015)

    Thanks andy...

    We have like after 40 characters the unit of measure is place in the description field. We have more than one space between the description and unit of measure.

    Does the UOM always begin at exactly the same place?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 17, 2015 at 7:32 am

    #1784490

    yes..Chris..

  • March 17, 2015 at 7:43 am

    #1784492

    Then try SUBSTRING(string, startpos, 8000) or calculate the correct length for the third parameter.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 17, 2015 at 7:46 am

    #1784493

    .. so why won't a sumple SUBSTRING(@str,from,length) do the job?

    (like wot Chris said! 🙂 )

  • March 17, 2015 at 11:21 am

    #1784559

    sqlquery29 (3/17/2015)

    Thanks andy...

    We have like after 40 characters the unit of measure is place in the description field. We have more than one space between the description and unit of measure.

    Please post a more complete sample of data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply