Replacing tags

Question

Post reply

Replacing tags

vidyap7

SSC-Addicted

Points: 457
More actions
May 25, 2005 at 1:40 pm

#67708

Hi,
I have a Template table which has the template of a letter, with each row corresponding to a line in the letter. I have tags in the letter between '<' and '>'. These tags need to be replaced with values from the Account table for each account the letter is being generated. I get each row into a variable @ParaInd from the Template table. Then I want to parse through that to replace the tags. The name of the tags is the name of the variable which contains the value it should be replaced with. For example, if the row had the text '<P2>some text <P3>', I want to replace the <P2> tag with the value in @P2 and replace <P3> with the value in @P3.
I want to avoid the if-elseif-elseif construct since I have about 20 tags. I wanted to build the REPLACE command dynamically and run it using the EXEC command. However, when I run the below code in SQL Query Analyzer, I get the error - 'Must declare the variable @ParaInd'.
Please help.
DECLARE @ParaInd nCHAR(20)
DECLARE @P2 CHAR(5)
DECLARE @P3 CHAR(5)
DECLARE @ParaValue CHAR(2)
DECLARE @iPStart INT
DECLARE @iPEnd INT
DECLARE @cTAGVALUEText nVARCHAR(150)
DECLARE @cTAGVALUEText2 nVARCHAR(150)
SELECT @ParaInd = '<P2><P3>'
SELECT @P2 = 'HELLO'
SELECT @P3 = 'BYE'
SELECT @iPStart = PATINDEX( '%<%',@ParaInd)
SELECT @iPEnd = PATINDEX( '%>%',@ParaInd)
SELECT @ParaValue = SUBSTRING(@ParaInd, @iPStart+1, @iPEnd - @iPStart)
SELECT @cTAGVALUEText = 'SELECT @cTAGVALUEText2 = REPLACE(@ParaInd, ''<' + @ParaValue + '>'', @' + @ParaValue + ')'
EXEC(@cTAGVALUEText)

Thanks,
Vidya.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Vidya - I don't have time to analyze your problem but your syntax seems to be incorrect in the last line...

try : SELECT @cTAGVALUEText = REPLACE(@ParaInd, '', '@' + @ParaValue)

**ASCII stupid question, get a stupid ANSI !!!**

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

The string you are building to execute has a variable in it. Proc variable aren't available to the executing string so I don't think this method will work.

Why worry about 'if-else' ?

Just do a replace for all the combinations. Whatever works works, whatever doesn't leaves the string unchanged.

SELECT @cTAGVALUEText2 = REPLACE(@cTAGVALUEText2, ''<P2>'', @P2)

SELECT @cTAGVALUEText2 = REPLACE(@cTAGVALUEText2, ''<P3>'', @P3)

And then again, I might be wrong ...
David Webb

vidyap7 SSC-Addicted Points: 457 More actions · Answer 3

Sushila,

That doesn't work. It gives me <A href="mailto:'@P2'">'@P2<P3>' instead of 'HELLO<P3>'.

David,

I might have more than one tag in a line. And I have more than 20 tags. So, having embedded replace will become cumbersome.

I know I can do this with a while construct with a case statement inside to achieve this.

WHILE @ParaInd like '%<%'

BEGIN

SELECT @iPStart = PATINDEX( '%<%',@ParaInd)

SELECT @iPEnd = PATINDEX( '%>%',@ParaInd)

SELECT @ParaValue = SUBSTRING(@ParaInd, @iPStart+1, @iPEnd - @iPStart)

SELECT @cTAGVALUEText = CASE @ParaValue

WHEN 'P2' THEN REPLACE (@ParaInd, '<P2>',@P2)

WHEN 'P3' THEN REPLACE(@ParaInd, '<P3>',@P3)

END

SELECT @ParaInd = @cTAGVALUEText

END

SELECT @cTAGVALUEText

However, I was wondering if there was a more elegant way of doing this by not worrying about the exact string I am replacing or its value.

Thanks,

Vidya.

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 4

Not a direct answer to your question, but related. I'm doing something similar using RTF templates but I'm doing my "replacing" in my ASP pages and feeding the values from XML elements, so it's a bit easier. The point I'll make here is... make sure none of the inputs to this template will contain "<" or ">" characters or you'll be in trouble. I ended up using vertical pipe ( | ) characters to delimit my fields in the templates.

Charles Cherry Mr or Mrs. 500 Points: 559 More actions · Answer 5

If you can do the replacing in a DLL or a web app, you might want to try a product called Aspose Word. We use it for form letters in our Web apps - it allows you to create Word mail-merge documents on the fly, very fast, without needing to have Microsoft Word installed on the server. You can save the documents to the file system, stream them back to the database, stream them to the browser, or whatever. Great tool, great company: http://www.aspose.com/

Calvin Lawson SSChampion Points: 11030 More actions · Answer 6

I'd recommend a simple technique like this. If your tags need different variables for each Row, you'd want to set this up as a Parent/Child relationship, like so:

Tag

TagID

Value

Customer

CustomerID

FirstName

LastName

Email

CustomerTagRepl

TagID

CustomerID

Value

------------------------------------------------------------------------------------------

---following should be a permanent table (or set of tables)

if object_ID('tempdb..#TagRepl') is not null drop table #TagRepl

create table #TagRepl

(

Tag varchar(100),

Repl varchar(100)

&nbsp

Insert #TagRepl values ('<FirstName>', 'Calvin')

Insert #TagRepl values ('<LastName>' , 'Lawson')

Insert #TagRepl values ('<Email>' , 'test@test.com')

declare @Text varchar(8000)

select @Text = 'Dear <FirstName> <LastName>,' + char(10) + 'Is <Email> your Email Address?'

select @Text = replace(@Text, Tag, Repl)

from #TagRepl

select @Text

Signature is NULL