Replace all values in string with values from "Look Up" table

Question

Replace all values in string with values from "Look Up" table

baovphoto

Old Hand

Points: 348
More actions
March 19, 2014 at 12:47 pm

#396447

I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
Thanks for your help!
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
VALUES( '[Sales Units]', '[cSalesUnits]' ),
( '[AUR]', '[cAUR]' ),
( '[Comp Sales Units]', '[cCompSalesUnits]' ),
( '[Comp AUR]', '[cCompAUR]' )
DECLARE @MathFormulas TABLE ( Formula VARCHAR(128) )
INSERT INTO @MathFormulas
( Formula )
VALUES( '([Sales Units]*[AUR])' ),
( '([Comp Sales Units]*[Comp AUR])' )
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Is there a reason to do this in SQL Server? Typically this sort of operation is best done in the application. For instance:

http://stackoverflow.com/questions/4763611/replace-multiple-words-in-string

K. Brian Kelley
@kbriankelley

baovphoto Old Hand Points: 348 More actions · Answer 2

I am trying to do it in SQL because this is going to be a one time event...I am importing that data from an Excel sheet, and then running a bunch of conversions on it to convert it into a "Configuration" table of sorts, that is then used else where to dynamically build a bunch of stuff.

I was just trying to find a way to do it in SQL since I have already imported it there and done my other manipulations there.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

As it is a one off, I will recommend this, which is dirty but works and is simple:

select 'hello world'; -- just here to "prime" @@rowcount

while @@rowcount>0

update mf

set formula = replace(formula,UserFriendlyName,SystemNames)

from @MathFormulas mf

join @synonyms s

on charindex(s.userfriendlyname,mf.formula)>0

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);