We have thousands of rows of product data we've received from a vendor; one column includes a barcode column. Within the column are multiple barcode vales, PIPE (|) delimited. The "barcodes" are sometimes ASIN, UPC, or a random "custom" barcode value (alphanumeric). I am trying to find a way to grab the 6 digit code that the vendor uses for the item. The barcode column is also not in order, sometime the 6 digit code is in the first delimited block, middle, or last block. How can I used substrings or split_strings to get the 6 digit code for each line? I keep running into errors for multiple substring or invalied parameter pass to left/substring function.
013206000509|810709
095198500151|213217|658514398083
621361|778522366830|750574368973
e.g.
(Select value from string_split(barcode,'|') where len(value) = '6')
SUBSTRING(barcode, LEN(LEFT(barcode, CHARINDEX ('|', barcode))) + 1, LEN(barcode) - LEN(LEFT(barcode, CHARINDEX ('|', barcode))) - LEN(RIGHT(barcode, LEN(barcode) - CHARINDEX ('|', barcode))) - 1)
Your initial attempt should work as a sub query.
SELECT *,
(Select Top (1) value from string_split(dataline,'|') x where len(value) = 6) as barcode
FROM ProductData