Table driven nested function calls

Question

I'm looking for a way to apply an arbitrary number of nested REPLACE calls on a column. Ideally the pattern and replace values will come from a configuration table, e.g.:

╔═════════╦═════════╗
║ Pattern ║ Replace ║
╠═════════╬═════════╣
║ -JAN-   ║ /01/    ║
║ -FEB-   ║ /02/    ║
║ -MAR-   ║ /03/    ║
║ -APR-   ║ /04/    ║
║ -MAY-   ║ /05/    ║
║ etc.    ║         ║
╚═════════╩═════════╝

The question is basically the same as this one, but the answer given only works when assigning the result of one input value at a time - I'd like to return the entire resultset.

I was thinking something like a recursive CTE might work..

Sample data

╔═══════════════╦══════════════╗
║     Input     ║    Output    ║
╠═══════════════╬══════════════╣
║ DataData-JAN- ║ DataData/01/ ║
║ -APR--MAY-    ║ /04//05/     ║
║ -MAR-TESTING  ║ /03/TESTING  ║
╚═══════════════╩══════════════╝

Could you please provide some sample data and desired outcome of the query? — Quassnoi, Jun 24 '14 at 14:07
If you take the linked solution and put it in a function, you could call it for each row in a resultset. — Jerrad, Jun 24 '14 at 14:15

Jerrad · Accepted Answer · 2014-06-24T15:38:29.770

Take the solution in the link that you mentioned, and put it in a function:

create table Replacement(ReplaceThis varchar(10), WithThis varchar(10))
insert into Replacement
    values  ('-JAN-', '/01/'), 
            ('-FEB-', '/02/'),
            ('-MAR-', '/03/'),
            ('-APR-', '/04/'),
            ('-MAY-', '/05/')

create table input(val varchar(100))
insert input
values ('DataData-JAN-'),
       ('-APR--MAY-'),
       ('-MAR-TESTING')

go

create function dbo.ReplaceValues(@input varchar(100))
returns varchar(100)
as
begin       
    select  @input = replace(@input, ReplaceThis, isnull(WithThis, ''))                       
    from    Replacement
    where   charindex(ReplaceThis, @input) > 0

    return @input
end

go

select val, dbo.ReplaceValues(val)
from input

SQL Fiddle

Table driven nested function calls

1 Answers1