sql server programming:
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) [quoted text, click to view] "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
>
>
>
>
>
minor correction, you can use varchar(8000), and double field size. if using ntext, then you are restricted to nvarchar(4000) -- bruce (sqlwork.com) [quoted text, click to view] "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
> >
> >
> >
> >
> >
>
>
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. [quoted text, click to view] "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
>
>
>
>
>
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
Don't see what you're looking for? Try a search.
|
