AdvancedHMI Software
General Category => Open Discussion => Topic started by: bachphi on June 13, 2020, 09:42:21 AM
-
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.
SELECT Barcode, REPLACE(Barcode, '[Z]{1}[0-9]{1,2}[B]{1}[0-9]{1,2}[S]{1}%', 'X') AS NewBarcode
FROM TB_BarcodeTag
Thank you for any help.
-
I found a clumsy way, but workable, I think:
1. First change the last char 'B' to something different like '&'. so Z10B10S10B ==> Z10B10S10X
/*
UPDATE TB_BarcodeTag2
SET Barcode = STUFF(Barcode, LEN(Barcode),1, '&')
WHERE Barcode LIKE 'Z%B%S%' AND Barcode like '%B'
*/
2. Replace Z,B, S to blank and '-'
/*
UPDATE TB_BarcodeTag2
SET Barcode = REPLACE(REPLACE(REPLACE(Barcode, 'Z', ''),'B','-'),'S','-')
WHERE Barcode LIKE 'Z%B%S%'
*/
3. Last, return the original 'B'
/*
UPDATE TB_BarcodeTag2
SET Barcode = STUFF(Barcode, LEN(Barcode),1, 'B')
WHERE Barcode LIKE 'Z%B%S%' AND Barcode like '%&'
*/