SQL replace string values

Question

I'd like to know how can I replace multiple text values from a string in SQL? I have a formula that I get from a table but inside that formula there are some text values with apostrophes that I need to replace for numeric values from another table, example:

Table_Values

ID| DESC |VALUE

01 | ABC | 5

02 | DEF    | 10

03 | GHI    | 15

TABLE_FORMULA

ID  | FORMULA

01  | X='ABC'+'DEF'+'GHI'   

The basic idea is to get the same formula with a result like this: X='5'+'10'+'15'

Any idea or example would be great. Thanks.

Answer 1

I don't know why your data is stored like that but here is my attempt to solve your problem.

First, you need a Pattern Splitter to parse your FORMULA. Here is one taken from Dwain Camp's article.

-- PatternSplitCM will split a string based on a pattern of the form 
-- supported by LIKE and PATINDEX 
-- 
-- Created by: Chris Morris 12-Oct-2012 
CREATE FUNCTION [dbo].[PatternSplitCM]
(
       @List                VARCHAR(8000) = NULL
       ,@Pattern            VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING 
AS 

RETURN
    WITH numbers AS (
        SELECT TOP(ISNULL(DATALENGTH(@List), 0))
            n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
        FROM
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
    )

    SELECT
        ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
        Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
        [Matched]
    FROM (
        SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
        FROM numbers
        CROSS APPLY (
            SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
        ) y
    ) d
    GROUP BY [Matched], Grouper

Here is your final query. This uses a combination of string functions like CHARINDEX, LEFT, RIGHT and string concatenation using FOR XML PATH(''):

WITH Cte AS(
    SELECT 
        f.*,
        LHS     = LEFT(f.FORMULA, CHARINDEX('=', f.FORMULA) - 1),
        RHS     = RIGHT(f.FORMULA, LEN(f.FORMULA) - CHARINDEX('=', f.FORMULA)), 
        s.*,
        v.VALUE
    FROM Table_Formula f
    CROSS APPLY dbo.PatternSplitCM(RIGHT(f.FORMULA, LEN(f.FORMULA) - CHARINDEX('=', f.FORMULA)), '[+-/\*]') s
    LEFT JOIN Table_Values v
        ON v.[DESC] = REPLACE(s.Item, '''', '')
)
--SELECT * FROM Cte
SELECT 
    c.ID,
    c.FORMULA,
    LHS + '=' + STUFF((
        SELECT ISNULL('''' + CONVERT(VARCHAR(5), VALUE) + '''', ITEM)
        FROM Cte
        WHERE ID = c.ID
        ORDER BY ItemNumber
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    , 1, 0, '')
FROM Cte c
GROUP BY C.ID, c.FORMULA, c.LHS

SQL Fiddle

RESULT

| ID |             FORMULA |                 |
|----|---------------------|-----------------|
|  1 | X='ABC'+'DEF'+'GHI' | X='5'+'10'+'15' |