First, I made a function to house the logic around the tags. I realize there are a few shortcomings here and currently, this means that you can't have a '<' in the text without it being the start of a full tag ending in '>', but there are a few adaptations that I feel would be easy enough to implement if this became problematic. The code is a modified version of a comma-delimited string parsing function I use a lot. Basically what this part does is gets the charindex of the opening and closing brackets and then length between the two. It cuts out the substring between them based on that length and pops it into a table. After it does that, it cuts off everything to the left of that closing bracket (or to be more accurate, it keeps the right side). This repeats until you have a table filled with the contents of all the bracketed terms in the string.
IF OBJECT_ID (N'dbo.TagParse') IS NOT NULL
DROP FUNCTION dbo.TagParse
GO
CREATE FUNCTION dbo.TagParse(@String varchar(max))
RETURNS @TagValues TABLE
(
ident int identity(1,1) primary key clustered,
TagValue varchar(100)
)
AS
begin
declare
@FragText varchar(255),
@OpenIndex int,
@CloseIndex int,
@FragLength int,
@StringLength int
--Get some relevant information about the frament length and position
select
@OpenIndex = CHARINDEX('<', @String) ,
@CloseIndex = CHARINDEX('>', @String),
@StringLength = datalength(@String)
select
@FragLength = @CloseIndex - @OpenIndex
while @CloseIndex > 0
begin
select @FragText = SUBSTRING(@String, @OpenIndex + 1, @FragLength -1)
select @String = RIGHT(@String, @StringLength - @CloseIndex)
insert into @TagValues(TagValue)
select @FragText
select
@OpenIndex = CHARINDEX('<', @String) ,
@CloseIndex = CHARINDEX('>', @String),
@StringLength = datalength(@String)
select
@FragLength = @CloseIndex - @OpenIndex
end
return
end
Once we've got the tags parsed out, we can get into getting those values from a presentation table. For the purposes of what I had to do, it will always come from the same table, but I suppose if one were so inclined, you could do some funky dynamic sql to be able to specify a table name too, but I won't go there (right now). I've built up some basic sample data so you can see this in action, but the idea behind it is to:
1) parse out the string using the above function
2) set a variable = the fact name parsed from the string
3) retrieve the value from the presentation table based on the fact name from step 2
Then, much to my dismay, using a loop, I go through the parsed table and replace instances of the tag with the values I just set. If there's a set based way to do this, I'd love to know it, but I couldn't think of a way that didn't involve dynamic sql and lots of nested replace statements. And given that these tagged values are probably not ever going to be more than a few tags here and there, the strain of RBAR is not too bad.
if OBJECT_ID('tempdb.dbo.#FactSet') > 0 drop table #FactSet
create table #FactSet
(
FactName varchar(100),
FactValue varchar(100)
)
insert into #FactSet (FactName, FactValue)
select 'FactName1', 'Test Value 1' union all
select 'FactName2', '3.14'
declare @Tags table
(
ident tinyint primary key clustered,
TagValue varchar(100)
)
declare
@String varchar(max),
@ident tinyint,
@MaxIdent tinyint,
@FactName varchar(255),
@FactValue varchar(255)
--This is the string that will constitute the disclaimer text they want to chop up
select @String = 'This is a test string. Field 1: , Field 2: '
--Parse the string into its constituent tags
insert into @Tags (ident, TagValue)
select ident, TagValue
from sysmon.dbo.TagParse(@String)
--Get the max ident through @@rowcount so I dont have to actually hit the @Tags table
select
@Ident = 1,
@MaxIdent = @@rowcount
while @ident <= @MaxIdent
begin
--Get the name of the fact name we parsed and assign it to @FactName
select @FactName = TagValue
from @Tags
where ident = @ident
--Get the value from the Fact Set table which corresponds to @FactName
select @FactValue = FactValue
from #FactSet
where FactName = @FactName
--We then replace any instances of the original tag with the value we just got.
--Enclosing the @FactName in <> to simulate the original tag syntax.
select @String = REPLACE(@String, '<' + @FactName + '>', @FactValue)
select @ident = @ident + 1
end
select @string
If anyone who reads this understands what the hell I just did, I'd be happy to hear any ways you think this could be done better.
