How can I parse through dynamic strings to pull out data? Example Below: (Written in T-SQL for SQL Server 2008 R2)

The Date to parse through:

BUSINESS=12^REFERENCE=9255^ACCOUNT_TYPE=SUPPLIER^SHIPPING_ID=ACHP^

I need the REFERENCE number when the ACCOUNT_TYPE=SUPPLIER.

The Reference Number can be from 1 to 16 characters in length.

My SQL-statement would look something like this:

SELECT <REFERENCE NUMBER> FROM ACCOUNTS

The result would look something like this:

9255
84
1
151221
415
99
etc...

You should normalize your data. But here is a solution.

declare @accounts table(col1 varchar(max))

insert @accounts values('BUSINESS=12^REFERENCE=9255^ACCOUNT_TYPE=SUPPLIER^SHIPPING_ID=ACHP^')

SELECT replace(data, 'REFERENCE=', '') FROM
(
SELECT t.c.value('.', 'VARCHAR(2000)') data
FROM (
    SELECT x = CAST('<t>' +
        REPLACE(col1, '^', '</t><t>') + '</t>' AS XML)
    FROM @accounts
    WHERE col1 like '%ACCOUNT_TYPE=SUPPLIER%'
) a
CROSS APPLY x.nodes('/t') t(c)
) x
WHERE data like 'REFERENCE=%'

Result:

9255

Fix your data, that will save you from alot of grief

more