Replace List

9 Dec 2005 4:48 PM

Eric D.

Hi,

I was wondering if SQL Server had a function similar to the ReplaceList()
function in ColdFusion. If you don't know anything about ColdFusion, I'll
explain what I need.

I have a field that I need to verify for "bad" characters from a list and
replace them with the "good" versions.

For example:
ArrayBad('badchar_1', 'badchar_2', 'badchar_3', ...);
ArrayGood('goodchar_1', 'goodchar_2', 'goodchar_3', ...);

Replace all the listed bad characters with their equal good version
repectively.

Anyone now how I can do this in a UDF format?

TIA,
Eric

9 Dec 2005 5:11 PM

Aaron Bertrand [SQL Server MVP]

T-SQL doesn't have any array or list datatypes. The most direct approach is
that you'll have to nest them. E.g.

CREATE FUNCTION dbo.CleanWord
(
    @dirtyWord VARCHAR(64)
)
RETURNS VARCHAR(64)
AS
BEGIN
    DECLARE @cleanWord VARCHAR(32);

    SET @cleanWord =
        REPLACE(
            REPLACE(
                @dirtyWord,
                'bleep','@#%$'),
            'beep','#^&@');

    RETURN @cleanWord;
END
GO

SELECT dbo.CleanWord('who the bleep did this beep?');
GO

DROP FUNCTION dbo.CleanWord;
GO

Show quote

"Eric D." <Er***@discussions.microsoft.com> wrote in message
news:0BD58B41-D2D8-45F6-ADDA-5707717C7C65@microsoft.com...
> Hi,
>
> I was wondering if SQL Server had a function similar to the ReplaceList()
> function in ColdFusion. If you don't know anything about ColdFusion, I'll
> explain what I need.
>
> I have a field that I need to verify for "bad" characters from a list and
> replace them with the "good" versions.
>
> For example:
> ArrayBad('badchar_1', 'badchar_2', 'badchar_3', ...);
> ArrayGood('goodchar_1', 'goodchar_2', 'goodchar_3', ...);
>
> Replace all the listed bad characters with their equal good version
> repectively.
>
> Anyone now how I can do this in a UDF format?
>
> TIA,
> Eric
>