Field Parsing Question

[mlager]

I have a single field in MS Sql Server 2005 that has the following format:

NOTES
-----
{VALUE1} AAA {VALUE2} BBB {VALUE3} CCC

What I'm trying to do is parse this field out to throw each value into another table. So it would parse it out like so:

VALUE1 VALUE2 VALUE3
------ ------ ------
AAA BBB CCC

Any text after {VALUE1} would belong to VALUE1, and so on. Any help would be greatly appreciated!

 

[bborissov]

Why not use XML?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mlager]

I'm trying to retrofit an existing system that requires people do enter additional fields for patients. They came up with a solution to put it in a "notes" field with the format described above. So I need to extract out that notes field and parse out the fields. It sucks

 

[cmmrfrds]

Check into regular expressions, they are useful for handling patterns like you show in your example.

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

 

[memdiscount]

Hi;

I could provide you something like this, which you can see how does it fit to your requirement.

***************************************************
Declare @table table (Column1 varchar(10) , Column2 varchar(50) )

Declare @L_STR varchar(100) , @Column1 varchar(10) , @Column2 varchar(50)

SET @L_STR = '{VALUE1} AAA {VALUE2} BBB {VALUE3} CCC'

Declare @L_Counter int
Declare @L_LoopCount int

SET @L_Counter = 1
SET @L_LoopCount = len(@L_STR) - len(Replace(@L_STR , '{' , '') )


WHILE @L_Counter <= @L_LoopCount

BEGIN
SET @Column1 = substring(@L_STR , 1 , charindex('}',@L_STR))
SET @L_STR = Replace(@L_STR , @Column1 , '')

SET @Column2 = substring(@L_STR , 1 , CASE WHEN charindex('{',@L_STR) = 0 Then Len(@L_STR) ELSE charindex('{',@L_STR) -1 END)
SET @L_STR = Replace(@L_STR , @Column2 , '')

INSERT INTO @table
SELECT Replace(Replace(@Column1, '{' , '') , '}','') , ltrim(rtrim(@Column2))
SET @L_Counter = @L_Counter + 1
END


SELECT * FROM @table

You may think of Pivot table to get your required result.

*********************************************

I hope it will help.

Thanks