How do I match a substring of variable length?

I am importing data into my SQL database from an Excel spreadsheet.

The 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.

Examples:

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).

Examples:

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?


Source: regex

Leave a Reply