SQL REPLACE command

[bachphi]

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.

Code: [Select]

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.

[bachphi]

I found a clumsy way, but workable, I think:

1. First change the last char 'B' to something different like '&'. so Z10B10S10B  ==> Z10B10S10X

Code: [Select]

/*
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 '-'

Code: [Select]

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

3. Last, return the original 'B'

Code: [Select]

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