t-sql user defined function to replace text with a lookup from a table

Question

UDF are not my cup of tea sadly and I'm using SQL2014

I want to use a UDF to replace text in a string by looking up the value to be replaced from a table with the replacement value in the same row in another column I managed to find this example below but that only removes the last row for the lookup table

Here is the one that is not quite right so I'm looking for one that will work please

USE [WebProductDataBase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Ufn_ReplaceSeller]
(
 @Value   varchar(max)
)
RETURNS  varchar(max)
AS
BEGIN

declare @Return   varchar(max)

SELECT @return = Replace(@Value, LookupValue, ReplaceValue) FROM dbo.Dim_Seller

RETURN @return
End

Here is an extract from my lookup table (currently the value we need to replaceValue is set to blank as that's is what we need to remove the LookupValue from the string) See example of the string below with the result needed

SellerID    Name            LookupValue      ReplaceValue   SellerUrl   DataID
AW2485      ActivInstinct   ActivInstinct                               1
AW6342      All Outdoor     All Outdoor                                 2
AW3023      Ambr            Amb                                         3
AW3469      Amp Bos         Amp Bos                                     4
AW988       Beau Expert     Beau Expert                                 5
AW222       Sloggi          Sloggi                                      6 
AW333       Ladies          Ladies                                      7

Source Row Field    Result Needed
Ladies 1 Pack Sloggi Invisible Supreme Midi Briefs
Ladies 1 Pack Sloggi Invisible Supreme Midi Briefs
Ladies 1 Pack Sloggi EverNew Cotton Midi Briefs
Ladies 1 Pack Sloggi EverNew Cotton Midi Briefs

The Better Option would be a multi replace function as I will need one to lookup and remove multiple values going forward
Source Row Field Result Needed Ladies 1 Pack Sloggi Invisible Supreme Midi Briefs Invisible Supreme Briefs Ladies 2 Packs Sloggi EverNew Cotton Midi Briefs EverNew Cotton Briefs

So the LookupValue would be     ReplaceValue
Sloggi                          Blank
Ladies                          Blank
Midi                            Blank
Packs                           Blank
Pack                            The rest below would also be blank for now
1   
2   
3   
4   
5   
6   
7   
8   
9   
0

By The A. This has spaces in front and at the end

This list will be quite long by the end of the project

Gordon Linoff · Accepted Answer · 2016-01-24 12:41:15Z

2

I think the following will work:

BEGIN
    declare @Return varchar(max);

    SET @Return = @Value;

    SELECT @return = Replace(@return, LookupValue, ReplaceValue)
    FROM dbo.Dim_Seller;

    RETURN @return;
End;

I'm not a big fan of using variables in functions like this (modifying the variable and setting it at the same time). This version should work, however, and is much simpler to code than a while loop using the table or a cursor and should be faster than a recursive CTE.

answered Jan 24, 2016 at 12:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

t-sql user defined function to replace text with a lookup from a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related