CODEWITHSUNDEEP
sqlsql-serverreplacesql-update
BachPhi
BachPhi

Reputation: 180

SQL Server replace different characters in a column value

I have a Barcode column with some data like below:

Z1B1S1A         -- Zone 1 Bay 1 Shelf 1A
Z10B10S10B      -- Zone 10 Bay 10 Shelf 10B

want to replace them with:

01-01-01A          -- I think I can get by with  1-1-1A
10-10-10B

The zone, bay, shelf can go from 1 to 99.

The problem for me is the inconsistent with 1 or 2 digit, and the last char 'B' can be mistaken for the Bay number.

Thank you for any help.

Upvotes: 0

Views: 75

Answers (3)

Deepshikha
Deepshikha

Reputation: 10274

It might look a bit messy but i think following code should help you:

First select the parts of string we are interested in:

declare @T1 table (barcode varchar(max))
insert into @T1 values('Z1B1S1A'),('Z10B10S10B'),('Z99B99S99C')

select substring(barcode,2,charindex('B',barcode)-2),
       substring(barcode,charindex('B',barcode)+1,
                 charindex('S',Barcode)-charindex('B',Barcode)-1),
       substring(barcode,charindex('S',barcode)+1,len(barcode))

from @T1

Now we can format as required: 

select right('00'+ substring(barcode,2,charindex('B',barcode)-2),2)
       +'-'+
       right('00' + 
              substring(barcode,charindex('B',barcode)+1,
              charindex('S',Barcode)-charindex('B',Barcode)-1),2)
       +'-'+
       right('000' + 
              substring(barcode,charindex('S',barcode)+1,
              len(barcode)),3)
       from @T1

Upvotes: 0

BachPhi
BachPhi

Reputation: 180

2 solutions. First solution is from help & idea of Analyst & DeepShiKha 1. First solution

-- SUBSTRING ( expression ,start , length )
-- CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
select substring(Barcode,2,charindex('B',barcode)-2) AS Zoney,
       substring(Barcode,charindex('B',Barcode)+1, charindex('S',Barcode)-charindex('B',Barcode)-1) AS Bay,
       substring(Barcode,charindex('S',Barcode)+1,len(Barcode)) AS Shelf,

concat (
        substring(Barcode,2,charindex('B',barcode)-2),'-',
        substring(Barcode,charindex('B',Barcode)+1, charindex('S',Barcode)-charindex('B',Barcode)-1), '-',
       substring(Barcode,charindex('S',Barcode)+1,len(Barcode)) 
       ) AS ZBS

from TB_BarcodeTag4 
where  Barcode LIKE 'Z%B%S%'


2. Second solution is from my own

    UPDATE TB_BarcodeTag4
    SET Barcode = STUFF(Barcode, LEN(Barcode),1, '&')
    WHERE Barcode LIKE 'Z%B%S%' AND Barcode like '%B'

    UPDATE TB_BarcodeTag4
    SET Barcode = REPLACE(REPLACE(REPLACE(Barcode, 'Z', ''),'B','-'),'S','-')
    WHERE Barcode LIKE 'Z%B%S%'

    UPDATE TB_BarcodeTag4
    SET Barcode = STUFF(Barcode, LEN(Barcode),1, 'B')
    WHERE Barcode LIKE 'Z%B%S%' AND Barcode like '%&'

Upvotes: 1

an.analyst
an.analyst

Reputation: 13

I am confident there is a much more beautiful way to do this but just wanted to give back to the forum that has given so much to me. Hope it helps.

This produces 4 columns.
1. Zone
2. Shelf
3. Bay
4. ZoneBayShelf (Last column is just a concatenation of previous three)

Used a combination of SUBSTR and LOCATE function to find the points at which to cut the BarCode. CONCAT just to bring it altogether.

SELECT
    SUBSTR(BARCODE,2,LOCATE("Z",BARCODE, 1)) AS ZONE,
    SUBSTR(BARCODE,LOCATE("B",BARCODE, 1),
        LOCATE("B",BARCODE, 1)-LOCATE("Z",BARCODE, 1)) AS BAY,
    SUBSTR(BARCODE,LOCATE("S",BARCODE, 1),
        LEN(BARCODE)) AS SHELF,
    CONCAT( SUBSTR(BARCODE,2,LOCATE("Z",BARCODE, 1)),
            "-", SUBSTR(BARCODE,LOCATE("B",BARCODE, 1),
              LOCATE("B",BARCODE, 1)-LOCATE("Z",BARCODE, 1)).
            "-", SUBSTR(BARCODE,LOCATE("S",BARCODE, 1),
              LEN(BARCODE))
          ) AS ZoneShelfBay 
FROM DATABASE

Upvotes: 0

Related Questions