String Replace column data with data from another table

Question

I have two tables:

TableA:

ID       Values
---------------
1         Q
2         B
3         TA
4         BS

TableB:

RawValue    Value
------------------
[1][4]      QBS
[2][1][3]   BQTA

I need to generate TableB values with its given RawValues. each [X] in rawvalue is the ID coulmn of TableA and shoud be replace with its value .

[1][4] means that Value of TableA with has ID of 1 (Q) and Value of TableA with has ID of 4 (BS) then should equal to QBS.

can anyone suggest a way to do it?

this is what I have already tried:

    update tableb set value=replace(rawvalue,'[' + (select id from tablea where id = cast(replace(replace(rawdata,'[',''),']','') as int)) + ']',
(select values from tablea where id = cast(replace(replace(rawdata,'[',''),']','') as int)))

By the way: this is still in test process and I can totally change tables, rowvalue format and replacement methods if anyone has a better idea.

GriGrim · Accepted Answer · 2014-03-18 13:28:28Z

2

declare @tableA table (id int, value varchar(50))
insert into @tableA (id, value)
select 1, 'Q' union all
select 2, 'B' union all
select 3, 'TA' union all
select 4, 'BS'

declare @tableB table (rawdata varchar(255), value varchar(255))
insert into @tableB (rawdata)
select '[1][4]' union all -- QBS
select '[2][1][3]' -- BQTA


update b
set value = (
        select a.value + ''
        from @tableA a
        cross apply (select charindex ('[' + cast (a.id as varchar(50)) + ']', b.rawdata) as pos) p
        where pos > 0
        order by pos
        for xml path('')
    )
from @tableB b

select * from @tableB

P.S. I would recommend not to name field similar to reserved keywords (I mean Values).

answered Mar 18, 2014 at 13:28

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

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

1 Comment

Ashkan Mobayen Khiabani Over a year ago

Thanks a lot it works like a charm. although I didn't understand a bit how code works.

Mikael Eriksson · Accepted Answer · 2014-03-18 13:27:51Z

Turn RawValue into XML, shred the XML to get one row for each value in RawValue and join to TableA to get the value.

Use the for xml path() trick to concatenate the values from TableA.

update TableB
set Value = (
            select T.Value as '*'
            from (
                 select row_number() over(order by T2.X) as SortOrder,
                        TableA.Value
                 from (select cast(replace(replace(TableB.RawValue, '[', '<x>'), ']', '</x>') as xml)) as T1(X)
                   cross apply T1.X.nodes('x') as T2(X)
                   inner join TableA
                     on TableA.ID = T2.X.value('text()[1]', 'int')
                 ) as T
            order by T.SortOrder
            for xml path('')
            )

SQL Fiddle

Collectives™ on Stack Overflow

String Replace column data with data from another table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related