2

I have a table and values like this

CREATE TABLE BarcodeTABLE
( 
     ID INT IDENTITY,
     BARCODE_VALUE nvarchar(max),
     GTIN2 nvarchar(MAX),
     HIBC NVARCHAR(max),
     UPC nvarchar(max),
     Others2 nvarchar(max)
)

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('012,56', '012', '', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('05,C50,25', 'C50', '25', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('1,2,3', '1', '', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('5,6,7', '', '', '7', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('8,9,10', '', '9', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('100,200,300', '100', '', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('A12,12', '', 'A12', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('B25,10', '', 'B25', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('H65,26,0', 'H65', '', '', '')

INSERT INTO BarcodeTABLE(BARCODE_VALUE, GTIN2, HIBC, UPC, OTHERS2) 
VALUES('H75,22,25', 'H75', '', '', '')

I need to get the output like as shown in the image, i.e the values which are not there in few columns should be there in the separate column as shown in the figure, give me the easiest way to achieve this:

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
natwar lal
  • 331
  • 3
  • 10
  • I don't want answer for splitting it, i just want to put resultant value whichever is not in other columns, please check the picture pasted, you will get the idea, I clearly said, i am not doing anything here with the split. and the values are already available, i just need to update to the Others2 column thats it – natwar lal May 04 '17 at 05:57
  • what is the criteria that you have taken to get the output of others2 column – Rams May 04 '17 at 06:08
  • update BarcodeTABLE set Others2=(case WHEN BARCODE_VALUE LIKE '%'+GTIN2+'%' THEN REPLACE(BARCODE_VALUE,GTIN2,'') WHEN BARCODE_VALUE LIKE '%'+HIBC+'%' THEN REPLACE(BARCODE_VALUE,HIBC,'') WHEN BARCODE_VALUE LIKE '%'+UPC+'%' THEN REPLACE(BARCODE_VALUE,UPC,'') ELSE BARCODE_VALUE END) – natwar lal May 04 '17 at 06:22
  • but that won't work properly as i needed – natwar lal May 04 '17 at 06:23

2 Answers2

1

I notice that 3 columns GTIN2, HIBC ,UPC is the same order with BARCODE_VALUE. You could concatenate 3 columns and replace like this

SELECT bt.ID, bt.BARCODE_VALUE, bt.GTIN2, bt.HIBC, bt.UPC,
     reverse(STUFF(reverse(STUFF(replace(',' + bt.BARCODE_VALUE + ',', 
               ',' +IIF(bt.GTIN2 = '', '', bt.GTIN2 + ',')
                + IIF(bt.HIBC = '', '', bt.HIBC+ ',') 
                + IIF(bt.UPC= '', '', bt.UPC+ ',') , ','), 1,1,'')),1,1,'')) AS Other2
FROM dbo.BarcodeTABLE bt

The result after replace has , at first and last, so use STUFF to remove it.
Demo link: http://rextester.com/PKTB64332
Hope it helps ...

TriV
  • 5,118
  • 2
  • 10
  • 18
0
SELECT BARCODE_VALUE,
CASE WHEN CHARINDEX(GTIN2 + ',', BARCODE_VALUE) > 0 OR CHARINDEX(',' + GTIN2, BARCODE_VALUE) > 0 THEN GTIN2 ELSE '' END AS 'GTIN2',
CASE WHEN CHARINDEX(HIBC + ',', BARCODE_VALUE) > 0 OR CHARINDEX(',' + HIBC, BARCODE_VALUE) > 0 THEN HIBC ELSE '' END AS 'HIBC',
CASE WHEN CHARINDEX(UPC + ',', BARCODE_VALUE) > 0 OR CHARINDEX(',' + UPC, BARCODE_VALUE) > 0 THEN UPC ELSE '' END AS 'UPC',
CASE WHEN UPC = '' THEN
    CASE WHEN HIBC = '' THEN
        CASE WHEN GTIN2 = '' THEN
            BARCODE_VALUE
        ELSE REPLACE(REPLACE(BARCODE_VALUE, GTIN2 + ',', ''), ',' + GTIN2, '') END
    ELSE REPLACE(REPLACE(CASE WHEN GTIN2 = '' THEN
                            BARCODE_VALUE
                         ELSE REPLACE(REPLACE(BARCODE_VALUE, GTIN2 + ',', ''), ',' + GTIN2, '') END, HIBC + ',', ''), ',' + HIBC, '')
    END
ELSE REPLACE(REPLACE(CASE WHEN HIBC = '' THEN
                         CASE WHEN GTIN2 = '' THEN
                            BARCODE_VALUE
                        ELSE REPLACE(REPLACE(BARCODE_VALUE, GTIN2 + ',', ''), ',' + GTIN2, '') END
                    ELSE REPLACE(REPLACE(CASE WHEN GTIN2 = '' THEN
                                            BARCODE_VALUE
                                        ELSE REPLACE(REPLACE(BARCODE_VALUE, GTIN2 + ',', ''), ',' + GTIN2, '') END, HIBC + ',', ''), ',' + HIBC, '')
                    END, UPC + ',', ''), ',' + UPC, '')
END AS 'OTHERS2'
FROM BarcodeTABLE
wannadream
  • 1,669
  • 1
  • 12
  • 14