SQL Compare varchar variable with another varchar variable

Question

I have a Table name lines which has BillId (int) and LineReference (Varchar(100) as two columns. Each billid has LineReference value. However, value in the LineReference might not be correct. So i have to validate the LineReference from a variable which has already has correct Reference value based on the bill id.

Example :

Declare @iCountRef varchar(100) = 1,2,3

BillId   LineReference
100      1,2,
100      1,2,40,34
100      1
100      12

From the above table, I need to update the LineReference column.

BillId   LineReference
100      1,2
100      1,2
100      1
100      1

I would be able to update only by comparing with the variable : @iCountRef. LineReference column should have the values in the @iCountRef. Whatever values are not there in @CountRef should be removed. If there is no matching values,then the column should be updated atleast with number 1.

Answer 1

--Create temp table and inserting data:
DECLARE @BillsRefs TABLE (
    BillId int, 
    LineReference nvarchar(100)
)

INSERT INTO @BillsRefs VALUES
(100,      '1,2,'),
(100,      '1,2,40,34'),
(100,      '1'),
(100,      '12')

--Declare variables
DECLARE @iCountRef varchar(100) = '1,2,3',
        @xml xml, @iXml xml

--Convert @iCountRef in XML
SELECT @iXml = CAST('<b>' + REPLACE(@iCountRef,',','</b><b>') + '</b>' as xml)

--@iXml:
--<b>1</b>
--<b>2</b>
--<b>3</b>

--Convert table with data in XML
SELECT @xml = (
SELECT CAST('<s id="'+LineReference+'"><a>' + REPLACE(LineReference,',','</a><a>') + '</a></s>' as xml) 
FROM @BillsRefs
FOR XML PATH('')
)
--@xml:
--<s id="1,2,">
--  <a>1</a>
--  <a>2</a>
--  <a />
--</s>
--<s id="1,2,40,34">
--  <a>1</a>
--  <a>2</a>
--  <a>40</a>
--  <a>34</a>
--</s>
--<s id="1">
--  <a>1</a>
--</s>
--<s id="12">
--  <a>12</a>
--</s>

--Compare values from temp table to @iCountRef
--we convert string to xml - to convert them intoi tables
;WITH final AS (
SELECT DISTINCT
        t.v.value('../@id','nvarchar(100)') as LineReferenceOld, -- @id to take 'id="1,2,40,34"' from xml above
        CASE WHEN s.g.value('.','int') IS NULL THEN 1 ELSE s.g.value('.','int') END as LineReference 
        -- '.' is used to take value inside closed tags
FROM @xml.nodes('/s/a') as t(v) --we takes @xml (look above) and play with its nodes 's' (root for each @id) and `a`
LEFT JOIN @iXml.nodes('/b') as s(g) --we takes @iXml it has only 'b' tags
    ON t.v.value('.','int') = s.g.value('.','int') --here we JOIN both xml by `a` and `b`  tags
)

--In final table we get this:

--LineReferenceOld  LineReference
--1,2,              2
--12                    1
--1,2,40,34         1
--1,2,40,34         2
--1                 1
--1,2,              1

--Final SELECT
SELECT c.BillId,
        STUFF((SELECT DISTINCT  ','+CAST(f.LineReference as nvarchar(10))
        FROM final f
        WHERE c.LineReference = f.LineReferenceOld
        FOR XML PATH('')),1,1,'') as LineReference
FROM @BillsRefs c 

Output:

BillId  LineReference
100     1,2
100     1,2
100     1
100     1

If you need to update source table:

UPDATE c
SET LineReference = STUFF((SELECT DISTINCT  ','+CAST(f.LineReference as nvarchar(10))
        FROM final f
        WHERE c.LineReference = f.LineReferenceOld
        FOR XML PATH('')),1,1,'')
FROM @BillsRefs c  

Answer 2

1) On medium or long term I would like to normalize this database in order to avoid such mistakes: storing list of values within string/VARCHAR columns. For example, I would use following many to many table:

CREATE TABLE dbo.BillItem (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    BilldID INT NOT NOT NULL REFERENCES dbo.Bill(BilldID),
    ItemID INT NOT NULL REFERENCES dbo.Item(ItemID),
    UNIQUE (BillID, ItemID) -- Unique constraint created in order to prevent duplicated rows
);

In this case, one bill with two items means I have to insert two rows into dbo.BillItem table.

2) Back to original request: for one time task I would use XML and XQuery thus (this solution ends with a SELECT statement but it's trivial to convert into UPDATE):

DECLARE @iCountRef VARCHAR(100) = '1,2,3'

DECLARE @SourceTable TABLE (
    BillId          INT,
    LineReference   VARCHAR(8000)
)

INSERT @SourceTable (BillId, LineReference)
VALUES
(100, '1,2,'),
(100, '1,2,40,34'),
(100, '1'),
(100, '12')

DECLARE @iCountRefAsXML XML = CONVERT(XML, '<a><b>' + REPLACE(@iCountRef, ',', '</b><b>') + '</b></a>')

SELECT  *, STUFF(z.LineReferenceAsXML.query('
    for $i in (x/y)
        for $j in (a/b)
            where data(($i/text())[1]) eq data(($j/text())[1])
        return concat(",", ($i/text())[1])
').value('.', 'VARCHAR(8000)'), 1, 1, '') AS NewLineReference
FROM (
    SELECT  *, CONVERT(XML, 
        '<x><y>' + REPLACE(LineReference, ',', '</y><y>') + '</y></x>' + 
        '<a><b>' + REPLACE(@iCountRef, ',', '</b><b>') + '</b></a>'
    ) AS LineReferenceAsXML
    FROM    @SourceTable s
) z

Results:

BillId      LineReference  NewLineReference LineReferenceAsXML                                                      
----------- -------------  ---------------- ------------------------------------------------------------------------
100         1,2,           1 ,2             <x><y>1</y><y>2</y><y /></x><a><b>1</b><b>2</b><b>3</b></a>             
100         1,2,40,34      1 ,2             <x><y>1</y><y>2</y><y>40</y><y>34</y></x><a><b>1</b><b>2</b><b>3</b></a>
100         1              1                <x><y>1</y></x><a><b>1</b><b>2</b><b>3</b></a>                          
100         12             (null)           <x><y>12</y></x><a><b>1</b><b>2</b><b>3</b></a>