I am importing data into my SQL database from an Excel spreadsheet.
imp table is the imported data, the
app table is the existing database table.
app.ReceiptId is formatted as “A” followed by some numbers. Formerly it was 4 digits, but now it may be 4 or 5 digits.
A1234 A9876 A10001
imp.ref is a free-text reference field from Excel. It consists of some arbitrary length description, then the ReceiptId, followed by an irrelevant reference number in the format ” – BZ-0987654321″ (which is sometimes cropped short, or even missing entirely).
SHORT DESC A1234 - BZ-0987654321 LONGER DESCRIPTION A9876 - BZ-123 REALLY LONG DESCRIPTION A2345 - B REALLY REALLY LONG DESCRIPTION A23456
The code below works for a 4-digit
ReceiptId, but will not correctly capture a 5-digit one.
UPDATE app SET [...] FROM imp INNER JOIN app ON app.ReceiptId = right(right(rtrim(replace(replace(imp.ref,'-',''),'B','')),5) + rtrim(left(imp.ref,charindex(' - BZ-',imp.ref))),5)
How can I change the code so it captures either 4 (A1234) or 5 (A12345) digits?