Home SQL Server: Replace values in field using lookup other table
Reply: 2

SQL Server: Replace values in field using lookup other table

Dhruv
1#
Dhruv Published in 2017-12-06 13:28:20Z

Let's say I have below table script

DECLARE @result TABLE
   (
    [ID] Int
   ,[Data] Varchar(500)
   )
DECLARE @codes TABLE
   (
    [ID] Varchar(500)
   ,[FullNames] Varchar(500)
   )
INSERT   INTO @result
         SELECT   1
                 ,'[A]-[B]'
INSERT   INTO @result
         SELECT   2
                 ,'[D]-[A]'
INSERT   INTO @result
         SELECT   3
                 ,'[A]+[C]'

INSERT   INTO @codes
         SELECT   'A'
                 ,'10'
INSERT   INTO @codes
         SELECT   'B'
                 ,'20'
INSERT   INTO @codes
         SELECT   'C'
                 ,'30'
INSERT   INTO @codes
         SELECT   'D'
                 ,'40'

SELECT * FROM @result
SELECT * FROM @codes

Output of those are as below: @result

ID  Data
--  -------
1   [A]-[B]
2   [D]-[A]
3   [A]+[C]

@codes

ID  FullNames
--  -------
A   10
B   20
C   30
D   40

Now I want output as below also:

ID  Data
--  -----
1   10-20
2   40-10
3   10+30

Please help me. Please note: Data columns also contains ([A]-[B]+[D])*[C]

I found similar solution on https://stackoverflow.com/a/26650255/8454103 which is for your reference.

Rainman
2#
Rainman Reply to 2017-12-06 13:53:15Z

Try like this;

select Output from (
select Data, c1.FullNames as LeftSideName, c2.FullNames as RightSideName, LeftSide, RightSide, REPLACE(REPLACE(Data,'[' + LeftSide + ']',c1.FullNames),'[' + RightSide + ']',c2.FullNames) as Output from (
select r.ID, Data,SUBSTRING(Data, 2, 1) LeftSide ,SUBSTRING(Data, 6, 1) RightSide from @result r ) Result
inner join @codes c1 ON Result.LeftSide = c1.ID
inner join @codes c2 ON Result.RightSide = c2.ID)
Records

Output:
10-20
10+30
40-10

The query can perform replacing dynamically A,B,C,D,E according to @codes table etc.

Michał Turczyn
3#
Michał Turczyn Reply to 2017-12-06 13:53:29Z

Try this:

select ID, REPLACE(REPLACE(REPLACE(REPLACE(Data,
            '[A]', (select FullNames from @codes where ID = 'A')),
            '[B]', (select FullNames from @codes where ID = 'B')),
            '[C]', (select FullNames from @codes where ID = 'C')),
            '[D]', (select FullNames from @codes where ID = 'D'))
from @result
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303426 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO