Posted by catch on 10/10/05 17:06

Hi,

Suppose I have a table something like:

name (VARCHAR(64)) age (INT) quotation (TEXT)
=================================================
Donald Trump 50 I am rich
Bugs Bunny 26 What's up doc
...

and a template string something like:

SET @template = 'My name is {name}, my age is {age}, and I always say
"{quotation}".'

I'd like to be able to dynamically replace the placeholders in the
template string with values extracted from the corresponding columns in
the table, so I'd get a set of results like:

'My name is Donald Trump, my age is 50, and I always say "I am rich".'

The best I've come up with so far is:

SET @Query = 'SELECT '''
+ REPLACE(REPLACE(@String, '{', '''+CONVERT(varchar,'),
'}', ')+''')
+ ''' FROM Table'
EXEC (@Query)

This converts the template string into a query string, casting
everything to a
varchar to allow numeric values to work. In this case it would be:

SELECT 'My name is '+CONVERT(varchar,name)+', my age is '
+CONVERT(varchar,age)+', and I always say "'
+CONVERT(varchar,quotation)+'".'

The problem with this is that if the length of a varchar is
unspecified, it defaults to 30, which truncates long string values.

Can anyone figure out a way round this, or perhaps an alternative
method entirely?

--
Oli

• Next in forum: ...Contains more than the maximum number of prefixes. The maximum is 3.
• Prev in forum: Sql Server backup vs HP Data Protector (HPDP) -- Do *not* use SQL Server?
• Thread view: Template function

[Reply to this message]