need something like replace but not quite

I need a function that does the following:
SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z', 'The
Letter Z', ...)
Where if the input value is 'X' then 'The Letter X' is returned and so on. I
could write a function for this of course but I was hoping one exists in SQL
2k. I looked and couldn't find such an animal.
Thanks,
KeithKeith,
Try:
DECLARE @.LETTER CHAR(1)
SET @.LETTER = 'X'
SELECT 'The Letter ' + @.LETTER
--OR IN A FUNCTION
CREATE FUNCTION UFN_LETTER (@.LETTER CHAR(1))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @.STRING VARCHAR(20)
SET @.STRING = 'The Letter ' + @.LETTER
RETURN(@.STRING)
END
SELECT DBO.UFN_LETTER('X')
HTH
Jerry
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:elEXtN4zFHA.2008@.TK2MSFTNGP10.phx.gbl...
>I need a function that does the following:
> SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z',
> 'The
> Letter Z', ...)
> Where if the input value is 'X' then 'The Letter X' is returned and so on.
> I
> could write a function for this of course but I was hoping one exists in
> SQL
> 2k. I looked and couldn't find such an animal.
> Thanks,
> Keith
>|||"Keith G Hicks" wrote:

>I need a function that does the following:
> SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z',
> 'The
> Letter Z', ...)
> Where if the input value is 'X' then 'The Letter X' is returned and so on.
> I
> could write a function for this of course but I was hoping one exists in
> SQL
> 2k. I looked and couldn't find such an animal.
> Thanks,
> Keith
What about CASE ?
CASE InputValue
WHEN 'X' THEN 'The Letter X'
WHEN 'Y' THEN 'The Letter Y'
WHEN 'Z' THEN 'The Letter Z'
ELSE 'No Letter'
END
Craig|||I think he's looking for REPLACE with an unlimited arg list, like COALESCE
... sorry Keith, no can do. You could write your own function with a lot of
parameters though but you have to pass at least DEFAULT for every single one
even if they have a default value.
Another alternative would be to accept a specially formatted string that
contained your find & replace values, parse the string and use REPLACE in a
loop.
Good luck
"Jerry Spivey" wrote:

> Keith,
> Try:
> DECLARE @.LETTER CHAR(1)
> SET @.LETTER = 'X'
> SELECT 'The Letter ' + @.LETTER
> --OR IN A FUNCTION
> CREATE FUNCTION UFN_LETTER (@.LETTER CHAR(1))
> RETURNS VARCHAR(20)
> AS
> BEGIN
> DECLARE @.STRING VARCHAR(20)
> SET @.STRING = 'The Letter ' + @.LETTER
> RETURN(@.STRING)
> END
> SELECT DBO.UFN_LETTER('X')
> HTH
> Jerry
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:elEXtN4zFHA.2008@.TK2MSFTNGP10.phx.gbl...
>
>|||KH is correct. That is what I was looking for (my example was a bit too
simplistic
I guess). Thanks for the confirmation. -keith
"KH" <KH@.discussions.microsoft.com> wrote in message
news:87791D9A-7D88-4EF3-8713-C007B7476267@.microsoft.com...
I think he's looking for REPLACE with an unlimited arg list, like COALESCE
... sorry Keith, no can do. You could write your own function with a lot of
parameters though but you have to pass at least DEFAULT for every single one
even if they have a default value.
Another alternative would be to accept a specially formatted string that
contained your find & replace values, parse the string and use REPLACE in a
loop.
Good luck
"Jerry Spivey" wrote:

> Keith,
> Try:
> DECLARE @.LETTER CHAR(1)
> SET @.LETTER = 'X'
> SELECT 'The Letter ' + @.LETTER
> --OR IN A FUNCTION
> CREATE FUNCTION UFN_LETTER (@.LETTER CHAR(1))
> RETURNS VARCHAR(20)
> AS
> BEGIN
> DECLARE @.STRING VARCHAR(20)
> SET @.STRING = 'The Letter ' + @.LETTER
> RETURN(@.STRING)
> END
> SELECT DBO.UFN_LETTER('X')
> HTH
> Jerry
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:elEXtN4zFHA.2008@.TK2MSFTNGP10.phx.gbl...
on.
>
>|||On Wed, 12 Oct 2005 19:19:33 -0400, Keith G Hicks wrote:

>I need a function that does the following:
>SOMEFUNCTION(InputValue, 'X', 'The Letter X', 'Y', 'The Letter Y', 'Z', 'Th
e
>Letter Z', ...)
>Where if the input value is 'X' then 'The Letter X' is returned and so on.
I
>could write a function for this of course but I was hoping one exists in SQ
L
>2k. I looked and couldn't find such an animal.
Hi Keith,
You could use nested REPLACE:
REPLACE(REPLACE(REPLACE(InputValue, 'X', 'TheLetterX'), 'Y',
'TheLetterY'), 'Z', 'TheLetterZ')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)