Professional Web Applications Themes

Regular Expressions in SELECT statement? - Microsoft SQL / MS SQL Server

Hi, I need to separate certain data in a large text field into distinct columns, however I need to use regular expression to extract the data, as it is field delimited, as below: "Value=123|Description=hello, world!|Data=123456ABCDE|" "Description=goodbye, world!|Value=345|MoreData=ABCDE123456|" The only information I have about the data in this text field, is that it is all in a "<fieldname>=<value><delimiter>" format, and the order of the tag-value pairs is not necessarily the same for all rows. Also, some rows have more fields than others. I'd like to be able to extract the data into columns (e.g. the above row would have 3 columns: ...

  1. #1

    Default Regular Expressions in SELECT statement?

    Hi,

    I need to separate certain data in a large text field into distinct columns,
    however I need to use regular expression to extract the data, as it is field
    delimited, as below:

    "Value=123|Description=hello, world!|Data=123456ABCDE|"
    "Description=goodbye, world!|Value=345|MoreData=ABCDE123456|"

    The only information I have about the data in this text field, is that it is
    all in a "<fieldname>=<value><delimiter>" format, and the order of the
    tag-value pairs is not necessarily the same for all rows. Also, some rows
    have more fields than others.

    I'd like to be able to extract the data into columns (e.g. the above row
    would have 3 columns: Value, Description, Data). For any particular SELECT
    statement, extra tag-value columns would be ignored, and non-existent ones
    would have a value of NULL.

    So something like (pseudocode follows):
    SELECT regexp("Value=*|") AS Value, regexp("Description=*|") AS Description,
    regexp("Data=*|") AS Data
    FROM TableName

    Should return
    "123", "hello, world!", "123456ABCDE"
    "345", "goodbye, world!", NULL

    Any ideas?

    Thanks
    Alex





    Alex M Guest

  2. #2

    Default Re: Regular Expressions in SELECT statement?

    Ugh, string parsing


    CREATE TABLE blat(txt VARCHAR(8000))

    INSERT blat VALUES('Value=123|Description=hello, world!|Data=123456ABCDE|')

    INSERT blat VALUES('Description=goodbye,
    world!|Value=345|MoreStuff=ABCDE123456|')




    SELECT
    Value = CASE WHEN CHARINDEX('Value=', txt) > 0 THEN SUBSTRING(
    txt,
    CHARINDEX('Value=', txt) + 6,
    CHARINDEX('|', SUBSTRING(txt, CHARINDEX('Value=', txt) + 6, LEN(txt))) - 1
    ) END,
    Description = CASE WHEN CHARINDEX('Description=', txt) > 0 THEN SUBSTRING(
    txt,
    CHARINDEX('Description=', txt) + 12,
    CHARINDEX('|', SUBSTRING(txt, CHARINDEX('Description=', txt) + 12,
    LEN(txt))) - 1
    ) END,
    Data = CASE WHEN CHARINDEX('Data=', txt) > 0 THEN SUBSTRING(
    txt,
    COALESCE(CHARINDEX('Data=', txt) + 5, LEN(txt)),
    CHARINDEX('|', SUBSTRING(txt, CHARINDEX('Data=', txt) + 5, LEN(txt))) - 1
    ) END,
    MoreStuff = CASE WHEN CHARINDEX('MoreStuff=', txt) > 0 THEN SUBSTRING(
    txt,
    CHARINDEX('MoreStuff=', txt) + 10,
    CHARINDEX('|', SUBSTRING(txt, CHARINDEX('MoreStuff=', txt) + 10,
    LEN(txt))) - 1
    ) END
    FROM blat

    DROP TABLE blat


    Two problems... (1) you need an expression for each "field" you have in you
    pipe-delimited text, and (2) to do this with one single pass of the table,
    you can't have "fields" that are partial names of other fields... this is
    why I changed MoreData to MoreStuff.

    In other words, maybe you should consider normalization.





    "Alex M" <nospamhotmail.com> wrote in message
    news:KIZLa.34$6W.19newreader.ukcore.bt.net...
    > Hi,
    >
    > I need to separate certain data in a large text field into distinct
    columns,
    > however I need to use regular expression to extract the data, as it is
    field
    > delimited, as below:
    >
    > "Value=123|Description=hello, world!|Data=123456ABCDE|"
    > "Description=goodbye, world!|Value=345|MoreData=ABCDE123456|"
    >
    > The only information I have about the data in this text field, is that it
    is
    > all in a "<fieldname>=<value><delimiter>" format, and the order of the
    > tag-value pairs is not necessarily the same for all rows. Also, some rows
    > have more fields than others.
    >
    > I'd like to be able to extract the data into columns (e.g. the above row
    > would have 3 columns: Value, Description, Data). For any particular SELECT
    > statement, extra tag-value columns would be ignored, and non-existent ones
    > would have a value of NULL.
    >
    > So something like (pseudocode follows):
    > SELECT regexp("Value=*|") AS Value, regexp("Description=*|") AS
    Description,
    > regexp("Data=*|") AS Data
    > FROM TableName
    >
    > Should return
    > "123", "hello, world!", "123456ABCDE"
    > "345", "goodbye, world!", NULL
    >
    > Any ideas?
    >
    > Thanks
    > Alex
    >
    >
    >
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: Regular Expressions in SELECT statement?

    pretty trival, but you will be restricted to a 4k value for any tag.


    create function dbo.GetValue(
    s text,
    fn varchar(4000))
    returns varchar(4000)
    as
    begin
    declare i int,
    i2 int,
    v varchar(4000)

    set i = patindex('%|' + fn + '=%',s)
    if i = 0
    begin
    if left(cast(s as varchar(4000)),len(fn)) <> fn
    return null
    end
    begin
    set i = i + len(fn) + 1
    set i2 = charindex('|',s,i)
    set v = substring(s,i+1,i2-i-1)
    end
    return v
    end


    SELECT dbo.GetValue('Value',TextField) AS Value,
    dbo.GetValue('Description',TextField) AS Description,
    dbo.GetValue('Data',TextField) AS Data
    FROM TableName


    -- bruce (sqlwork.com)

    "Alex M" <nospamhotmail.com> wrote in message
    news:KIZLa.34$6W.19newreader.ukcore.bt.net...
    > Hi,
    >
    > I need to separate certain data in a large text field into distinct
    columns,
    > however I need to use regular expression to extract the data, as it is
    field
    > delimited, as below:
    >
    > "Value=123|Description=hello, world!|Data=123456ABCDE|"
    > "Description=goodbye, world!|Value=345|MoreData=ABCDE123456|"
    >
    > The only information I have about the data in this text field, is that it
    is
    > all in a "<fieldname>=<value><delimiter>" format, and the order of the
    > tag-value pairs is not necessarily the same for all rows. Also, some rows
    > have more fields than others.
    >
    > I'd like to be able to extract the data into columns (e.g. the above row
    > would have 3 columns: Value, Description, Data). For any particular SELECT
    > statement, extra tag-value columns would be ignored, and non-existent ones
    > would have a value of NULL.
    >
    > So something like (pseudocode follows):
    > SELECT regexp("Value=*|") AS Value, regexp("Description=*|") AS
    Description,
    > regexp("Data=*|") AS Data
    > FROM TableName
    >
    > Should return
    > "123", "hello, world!", "123456ABCDE"
    > "345", "goodbye, world!", NULL
    >
    > Any ideas?
    >
    > Thanks
    > Alex
    >
    >
    >
    >
    >

    bruce barker Guest

  4. #4

    Default Re: Regular Expressions in SELECT statement?

    minor correction, you can use varchar(8000), and double field size. if using
    ntext, then you are restricted to nvarchar(4000)

    -- bruce (sqlwork.com)


    "bruce barker" <nospam_brubarsafeco.com> wrote in message
    news:#bV2pBzPDHA.2248TK2MSFTNGP11.phx.gbl...
    > pretty trival, but you will be restricted to a 4k value for any tag.
    >
    >
    > create function dbo.GetValue(
    > s text,
    > fn varchar(4000))
    > returns varchar(4000)
    > as
    > begin
    > declare i int,
    > i2 int,
    > v varchar(4000)
    >
    > set i = patindex('%|' + fn + '=%',s)
    > if i = 0
    > begin
    > if left(cast(s as varchar(4000)),len(fn)) <> fn
    > return null
    > end
    > begin
    > set i = i + len(fn) + 1
    > set i2 = charindex('|',s,i)
    > set v = substring(s,i+1,i2-i-1)
    > end
    > return v
    > end
    >
    >
    > SELECT dbo.GetValue('Value',TextField) AS Value,
    > dbo.GetValue('Description',TextField) AS Description,
    > dbo.GetValue('Data',TextField) AS Data
    > FROM TableName
    >
    >
    > -- bruce (sqlwork.com)
    >
    > "Alex M" <nospamhotmail.com> wrote in message
    > news:KIZLa.34$6W.19newreader.ukcore.bt.net...
    > > Hi,
    > >
    > > I need to separate certain data in a large text field into distinct
    > columns,
    > > however I need to use regular expression to extract the data, as it is
    > field
    > > delimited, as below:
    > >
    > > "Value=123|Description=hello, world!|Data=123456ABCDE|"
    > > "Description=goodbye, world!|Value=345|MoreData=ABCDE123456|"
    > >
    > > The only information I have about the data in this text field, is that
    it
    > is
    > > all in a "<fieldname>=<value><delimiter>" format, and the order of the
    > > tag-value pairs is not necessarily the same for all rows. Also, some
    rows
    > > have more fields than others.
    > >
    > > I'd like to be able to extract the data into columns (e.g. the above row
    > > would have 3 columns: Value, Description, Data). For any particular
    SELECT
    > > statement, extra tag-value columns would be ignored, and non-existent
    ones
    > > would have a value of NULL.
    > >
    > > So something like (pseudocode follows):
    > > SELECT regexp("Value=*|") AS Value, regexp("Description=*|") AS
    > Description,
    > > regexp("Data=*|") AS Data
    > > FROM TableName
    > >
    > > Should return
    > > "123", "hello, world!", "123456ABCDE"
    > > "345", "goodbye, world!", NULL
    > >
    > > Any ideas?
    > >
    > > Thanks
    > > Alex
    > >
    > >
    > >
    > >
    > >
    >
    >

    bruce barker Guest

Similar Threads

  1. RE : RE : Regular expressions
    By Steve Hemond in forum PERL Beginners
    Replies: 4
    Last Post: December 17th, 08:53 PM
  2. RE : RE : RE : Regular expressions
    By Steve Hemond in forum PERL Beginners
    Replies: 3
    Last Post: December 17th, 07:38 PM
  3. PHP regular expressions
    By Albert in forum PHP Development
    Replies: 9
    Last Post: October 30th, 09:32 PM
  4. [PHP-DEV] PHP regular expressions
    By Vesselin Atanasov in forum PHP Development
    Replies: 0
    Last Post: August 5th, 08:19 PM
  5. Regular Expressions....HELP!
    By Stephajn Craig in forum ASP.NET General
    Replies: 1
    Last Post: July 16th, 06:56 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139