SQL replace string values
I would like to know how to replace multiple text values from a string in SQL? I have a formula that I am getting from a table, but inside this formula there are some text values with apostrophes that I need to replace with numeric values from another table, for 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 the result like this: X = '5' + '10' + '15'
Any idea or example would be great. Thank.
source to share
I don't know why your data is stored like this, but here is my attempt at solving your problem.
First you need a Template Splitter to analyze yours FORMULA
. Here is one taken from the Dwain Camp 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 request. It uses a combination of string functions, such as CHARINDEX
, LEFT
, RIGHT
and concatenation via 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
RESULT
| ID | FORMULA | |
|----|---------------------|-----------------|
| 1 | X='ABC'+'DEF'+'GHI' | X='5'+'10'+'15' |
source to share