16
Open Discussion / SQL REPLACE command
« 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.
Thank you for any help.
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.