Need to enter column values into text block

Question

Need to enter column values into text block

webskater

SSCarpal Tunnel

Points: 4214
More actions
July 20, 2009 at 4:53 am

#133973

I am storing some text in a text field that is, essentially, a pro-forma letter. The text contails html formatting.
When the text is retrieved it is placed in a .Net web application in a rich text box like the one I am typing in now. The letter is sent by email as the body of the email.
There are about 6 places within the text where I need to insert variables.
E.g. Dear variable
Re your enquiry about variable. I can advise you the matter will be considered by the variable committee etc.
So, I can retrieve my variables from one table ...
Select myLetterVariables FROM VariablesTbl
... and I can retrieve my text for the letter ...
Select LetterText From LettersTbl WHERE LetterType = @LetterType
The thing is - how can I insert the variables into the letter text in the right place.
I'm thinking along the lines of putting some sort of tag in the text ... so the text stored might look like
Dear
Re your enquiry about
etc.
But I can't really see how to do this - how do you find and replace '' with the actual value for the variable?
Hope it's clear.
Any help on how to do this would be much appreciated. I really want to do it within SQL Server and not have to do it on the client in C# (although I can do it there if I have to).

Viewing 4 posts - 1 through 3 (of 3 total)

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

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 1

sku370870 (7/20/2009)
I am storing some text in a text field that is, essentially, a pro-forma letter. The text contails html formatting.
When the text is retrieved it is placed in a .Net web application in a rich text box like the one I am typing in now. The letter is sent by email as the body of the email.
There are about 6 places within the text where I need to insert variables.
E.g. Dear variable
Re your enquiry about variable. I can advise you the matter will be considered by the variable committee etc.
So, I can retrieve my variables from one table ...
Select myLetterVariables FROM VariablesTbl
... and I can retrieve my text for the letter ...
Select LetterText From LettersTbl WHERE LetterType = @LetterType
The thing is - how can I insert the variables into the letter text in the right place.
I'm thinking along the lines of putting some sort of tag in the text ... so the text stored might look like
Dear
Re your enquiry about
etc.
But I can't really see how to do this - how do you find and replace '' with the actual value for the variable?
Hope it's clear.
Any help on how to do this would be much appreciated. I really want to do it within SQL Server and not have to do it on the client in C# (although I can do it there if I have to).

Its still fairly early on Monday morning.. but you *could* do something like:

DECLARE @myVar1 VARCHAR(100)

SET @myVar1 = (SELECT var1 FROM tblVar WHERE formLetter = 1)

DECLARE @myFormLetter VARCHAR(4000)

SET @myFormLetter = (SELECT textblock from tblFormLetter WHERE formLetter = 1)

SELECT REPLACE(@myFormLetter, 'var1', @myVar1) as myLetter

Thats probably not a nice way of doing it.. but its an option.. like I say.. its still early.. 🙂

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

I long for a job where my databases dont have any pesky users accessing them 🙂

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I think you'd be better off doing the replacement in .NET, but you could use the replacements above.

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 3

Thanks for your replies. Guess I'll do it in the front end.