Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    29

    Unanswered: Parasing the data from one colum to individual columns

    Here is my situation.

    I have data in SQLSever 2008 R2 in one column as following. I would like to run a sql statement and capture them into individual columns. Would that be possible? The column separator is |

    MSH|^~\&|HL7ADM|PYXIS|PAH|HL7PRX_PAH|201208131129 46||ZPM^ZPM|9442|P|2.3.1||||||| ZPM|C|console|N-HEART-ST|1|23|43025204|DOBUTAMINE 1000MCG/ML|U|1|1|1|CS3808|TEST, MONKEY|||1|||||0|1|0|20120813112839||||||||0||IV|| ||||||||||| 

  2. #2
    Join Date
    May 2005
    Posts
    119
    You would have to get creative using the statement:
    left(colname,CHARINDEX('|',colname))

    I'll break out the formula with you in case this is new.

    The basic formula is Left(columnname,#position) #position of the character point of the field to start) ie: if you use a 10, it will give you the first 10 characters of the field.

    To calculate the character position of the first bar, use the charindex(characteryourlookingfor,columnname) function.

    Put together, it will provide you with everything to the left of the |, wherever it's position. It may be easier to create the table with the columns output and put this string in the last position and use an update query each time you run it. You can then use the same formula to parse it out.

    For example, round 1 would be:
    left(colname,CHARINDEX('|',colname)) dump results to column1

    Round 2 would be
    left(right(colname,len(colname)-len(column1data),charindex('|', colname)) - dump results to column2

    Round 3 would be
    left(right(colname,len(colname)-(len(column1data)+len(column2data)),charindex('|', colname)) - dump results to column3

    ....so on and so forth until it is parsed out. make sense?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kmistry1 View Post
    I have data in SQLSever 2008 R2 in one column as following. 
    i'm going to go out on a limb and guess that this data wasn't assembled with SQL

    so why are looking to use SQL to disassemble it?

    why not use the same programming language that was used to create it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2012
    Posts
    30
    one way to do it is using UDF to parse out the individual values and then apply crosstabing to get them onto a same row.

    see below link for a similar function

    parse a string

    then use it like

    ;With ParseWordsTable
    AS
    (
    SELECT t.LongTextColumnName,f.Val,
    ROW_NUMBER() OVER (PARTITION BY t.LongTextColumnName ORDER BY f.ID) AS Seq
    FROM YourTable t
    CROSS APPLY dbo.ParseValues(t.LongTextColumnName,'|')f
    )

    SELECT MAX(CASE WHEN Seq=1 THEN Val END) AS Word1,
    MAX(CASE WHEN Seq=2 THEN Val END) AS Word2,
    MAX(CASE WHEN Seq=3 THEN Val END) AS Word3,
    ...
    FROM ParseWordsTable
    GROUP BY LongTextColumnName

  5. #5
    Join Date
    Aug 2012
    Posts
    45
    kmistry1, I'm gussing this data probably got there by importing from a text file or something like that. I think you would find it much easier and cleaner to re-import the data as pipe delimited text instead of a blob.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •