String Manipulation: REPLACE multiple variables at once through a Join: How to

[bill]

I want to convert this sample string:
"Some like <X> better than <Y>. I like <Y> better
than <X>, but my daughter prefers <X>."

To this one:
"Some like cats better than dogs. I like dogs better
than cats, but my daughter prefers cats."

Thus, <X> gets replaced with 'cats' and <Y> gets replaced
with 'dogs'.

One rather ugly way to do this is with variables and nested REPLACE
statements:
------------------------------------------------------
/*
create the variable for phrase_tx
*/
DECLARE @phrase_tx nvarchar(400)

/*
Use a CTE for the sample because
I don't feel like making a table
*/
WITH phrase as
(
SELECT
'Some like <X> better than <Y>. I like <Y> better
than <X>, but my daughter prefers <X>.'
AS phrase_tx
)

/*
Show the raw phase
*/
SELECT @phrase_tx = phrase_tx from phrase

/*
Show the "replaced" phrase
*/
SELECT @phrase_tx
SELECT replace(replace(@PHRASE_TX,'<X>','cats'),'<Y>','dogs')
------------------------------------------------------
This works, but each variable requires another level of nesting, and
the code quickly turns very nasty for maintenance.

I would much rather make a little table that had the variables and
their values:
------------------------------------------------------
create table
phrase_variable
(
variable_nm nvarchar(40)
,variable_value_tx nvarchar(100)
primary key (variable_nm)
)

insert into phrase_variable
(variable_nm, variable_value_tx) values ('<X>', 'cats')

insert into phrase_variable
(variable_nm, variable_value_tx) values ('<Y>', 'dogs')
------------------------------------------------------

Then, I would like to issue a statement that joins the phrase to the
substitution tables and does the REPLACES for all variables without
requiring lots of nesting.

I know in the ugly example above, I could avoid hard coding by setting
variables with SELECTs agains the table phrase_variable, but I want to
avoid the ugly nesting, and I'd like to eliminate the use of variables
altogether if possible.

Anyone know how to do what I would like?

Thanks,

Bill

[Hugo Kornelis]

On Thu, 17 Dec 2009 17:32:12 -0800 (PST), bill wrote:

>I want to convert this sample string:
>"Some like <X> better than <Y>. I like <Y> better
>than <X>, but my daughter prefers <X>."
>
>To this one:
>"Some like cats better than dogs. I like dogs better
>than cats, but my daughter prefers cats."
>
>Thus, <X> gets replaced with 'cats' and <Y> gets replaced
>with 'dogs'.

(snip)

>I would much rather make a little table that had the variables and
>their values:

(snip)

>Then, I would like to issue a statement that joins the phrase to the
>substitution tables and does the REPLACES for all variables without
>requiring lots of nesting.

Hi Bill,

If it's just the one phrase (in a variable), then this works on my
database:

SELECT @phrase_tx = REPLACE(@phrase_tx, variable_nm, variable_value_tx)
FROM phrase_variable
OPTION (MAXDOP 1);

I'm not 100% sure if this is fully documented and supported though.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

Hugo Kornelis (hu...@perFact.REMOVETHIS.info.INVALID) writes:
> If it's just the one phrase (in a variable), then this works on my
> database:
>
> SELECT @phrase_tx = REPLACE(@phrase_tx, variable_nm, variable_value_tx)
> FROM phrase_variable
> OPTION (MAXDOP 1);
>
> I'm not 100% sure if this is fully documented and supported though.

There is a KB article that documents this, and says that the behaviour is
undefined. http://support.microsoft.com/default.aspx?scid=287515.

Running a cursor over the table is the only safe I can think of right
now, but I'm in a bit of a hurry.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Plamen Ratchev]

The article describes the behavior of using ORDER BY as undefined. Even recommends the approach that Hugo used as
workaround solution (of course, the query doesn't use ORDER BY):

In order to achieve the expected results from an aggregate concatenation query, apply any Transact-SQL function or
expression to the columns in the SELECT list rather than in the ORDER BY clause.

But still, this variable select method with multiple rows returned has never been documented (unless we assume this KB
article implies aggregate concatenation is supported with the side effects in ORDER BY). Here is what BOL 2008 says:

SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the
name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is
assigned the last value that is returned.

One would assume based on the above and the fact that SQL operates "at once", not row by row, that only one of the
replacements will be applied, but still we see both apply.

Erland is correct that cursor is the reliable method (if the requirements are really to avoid nested REPLACE calls). You
can accomplish the same using a recursive CTE, but really not much different that a cursor solution.

--
Plamen Ratchev
http://www.SQLStudio.com

[bill]

Thanks everyone for the the replies. The SELECT statement that Hugo
showed looks terrific, and I would really like to use it, except for
the concerns raised in the KB article.

The article is not worded very clearly. However, when I read that
article, it appears that the "undefined" concern applies _only_ in the
case where functions are used in the WHERE clause.

Here is the sentence from the second paragraph that makes me think the
problem is related to WHERE clauses only:
"When an expression is applied to a member of an ORDER BY clause, that
resulting column is not exposed in the SELECT list, resulting in
undefined behavior."

IF this is the case (a big if), THEN I can use this SELECT statement
and not bother with the cursors.

Do people agree with my reading, or am I missing something?

Thanks,

Bill

[Erland Sommarskog]

Plamen Ratchev (Pla...@SQLStudio.com) writes:
> The article describes the behavior of using ORDER BY as undefined. Even
> recommends the approach that Hugo used as workaround solution (of
> course, the query doesn't use ORDER BY):

The article is certainly a bit confusing. But I prefer to hold on to
the first sentence:

The correct behavior for an aggregate concatenation query is undefined.

Nevermind that the article then stands on a head to state there is a
situation where it works nevertheless.

And usually it does work. But everyonce in a while, you see posts from
people who have been bitten.

[bill]

Thanks everyone. Based on Erland's clarification, I went with the
cursor. It works fine, because the table is small. I just HATE
cursors, which is why I was trying to avoid one. Maybe in a future
release they will fully define the query behavior?

Thanks,

Bill