"Alex M" <nospam@hotmail.com> wrote in message
news:KIZLa.34$6W.19@newreader.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" <nospam_brubar@safeco.com> wrote in message
news:#bV2pBzPDHA.2248@TK2MSFTNGP11.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" <nospam@hotmail.com> wrote in message
> news:KIZLa.34$6W.19@newreader.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
> >
> >
> >
> >
> >
>
>

"Alex M" <nospam@hotmail.com> wrote in message
news:KIZLa.34$6W.19@newreader.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
>
>
>
>
>