Parsing Out A String Into Separate Fields

  • I'm looking to parse out a string into separate fields and I'm looking for a good solution to do this. Here's the example dataset,

    CREATE TABLE DATA ( DATA1 varchar(50) null )

    INSERT INTO DATA ( DATA1 )

    VALUES ( '6:54-12345-ABC' )

    , ( '6-43-56789-DEF' )

    , ( '45-15432-6-GHI' )

    CREATE TABLE dbo.RESULTS ( Field1 varchar(1) NULL

    , Field2 varchar(2) NULL

    , Field3 varchar(5) NULL

    , Field4 varchar(4) NULL ) ON [PRIMARY]

    GO

    So, here's how it works. The char limitations are standard. The 6 in the data I provided (it doesn't have to be 6 but it will always be a single numerical digit) will always go into Field1; The two digit integer will always go into Field2, The 5 digit integer will always go into Field 3 and the 3 (or more) digit character field will always go into Field4. Basically I need to parse the data and dump it into those fields. I'm trying to think of an easy way to do this.

    I was thinking of simply looping through each record, stripping out each field with the rules i set above and inserting them as needed. I don't know if there's a better or quicker way to do this.

  • joshdbguy (12/7/2015)


    I'm looking to parse out a string into separate fields and I'm looking for a good solution to do this. Here's the example dataset,

    CREATE TABLE DATA ( DATA1 varchar(50) null )

    INSERT INTO DATA ( DATA1 )

    VALUES ( '6:54-12345-ABC' )

    , ( '6-43-56789-DEF' )

    , ( '45-15432-6-GHI' )

    CREATE TABLE dbo.RESULTS ( Field1 varchar(1) NULL

    , Field2 varchar(2) NULL

    , Field3 varchar(5) NULL

    , Field4 varchar(4) NULL ) ON [PRIMARY]

    GO

    So, here's how it works. The char limitations are standard. The 6 in the data I provided (it doesn't have to be 6 but it will always be a single numerical digit) will always go into Field1; The two digit integer will always go into Field2, The 5 digit integer will always go into Field 3 and the 3 (or more) digit character field will always go into Field4. Basically I need to parse the data and dump it into those fields. I'm trying to think of an easy way to do this.

    I was thinking of simply looping through each record, stripping out each field with the rules i set above and inserting them as needed. I don't know if there's a better or quicker way to do this.

    Certainly don't do any looping here. You just need to parse/split your strings. Ideally this kind of thing should not make it to the database but that isn't always possible. See the article in my signature about splitting strings and find the DelimitedSplit8K function.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This is perfect, thanks Sean.

  • You are quite welcome. Once you understand how that splitter works you will need to also understand how to do a crosstab. Assuming you always have 4 columns you do something like this.

    select MAX(case when ItemNumber = 1 then Item end) as Field1

    , MAX(case when ItemNumber = 2 then Item end) as Field2

    , MAX(case when ItemNumber = 3 then Item end) as Field3

    , MAX(case when ItemNumber = 4 then Item end) as Field4

    from data d

    cross apply dbo.DelimitedSplit8K(d.Data1, '-')

    group by d.DATA1

    You can learn more about crosstabs by following the article in my signature about crosstabs. If you need a dynamic number of columns you will need to follow the logic in the second of those about dynamic crosstabs. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

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