splitting a string and rebuilding from related data

Question

I've been given a string that can contain multiple offer codes separated by a tilde 123~125~126
I also have a table that may or may not define a parent/child relationship

tbl_allowed
============
offercode varchar(15)
parent_oc varchar(15)

ex:
offercode = 124
parent_oc = 126

I already have a function that will take the delimited string and split it, but I want to take the string, compare the contents to what's in tbl_allowed and regenerate it by replacing any values that exist in tbl_allowed in the parent_oc column with the value in the offercode column. If no defined relationship exists, then just use itself.

this is pretty simple with a single offercode:

set @newOfferCode = (select top 1 coalesce(cac.offercode, @lOfferCode)
from tbl_allowed cac
where OfferCode = @lOfferCode or parent_oc = @lOfferCode)

select coalesce(nullif(@newOfferCode,''), @lOfferCode)

but I'm having difficulty when I have a tilde delimited string. Any ideas?

If your splitting function returns a table then you can simply update the table with values from `tbl_allowed`. Then recombine the results into a single string. [This](http://stackoverflow.com/questions/1564980/building-a-comma-separated-list) shows how to build a comma delimited list. — HABO, Aug 07 '12 at 00:24

score 0 · Accepted Answer · answered Aug 08 '12 at 21:36

Here's what I ended up doing. Seems to work.

    CREATE TABLE #tempoffers(OfferCode varchar(15), NewOfferCode varchar(15))

    INSERT INTO #tempoffers
    SELECT OutParam, null
    FROM dbo.SplitString(@lOfferCode, '~')

    update #tempoffers set NewOfferCode = coalesce(cac.offercode, mb.offerCode)
    from #tempoffers mb
    left outer join tbl_Allowed cac on mb.OfferCode = cac.parent_oc

            -- building the new string
    declare @newOfferCode varchar(5000)
    SELECT @newOfferCode = COALESCE(@newOfferCode + '~', '') + NewOfferCode FROM #tempoffers

    drop table #tempoffers
    SELECT @newOfferCode as OfferCode

splitting a string and rebuilding from related data

1 Answers1