Parse Multiple Numeric Values

Question

question

tlenzmeier asked Jan 14, '20

Parse Multiple Numeric Values

I have a flat file where I can have multiple numeric values in a string. The numeric values vary in length. The numeric value that I am interested in has a length of at least 6 characters.

Here's an example: '2 referrals for CPS claim 135842' -- SUBJECT

My current code is

LEFT(SUBSTRING([SUBJECT], PATINDEX('%[0-9]%', [SUBJECT]), 8000), PatIndex('%[^0-9]%', SubString([SUBJECT], PatIndex('%[0-9]%', [SUBJECT]), 8000) + 'X')-1)

This result is 2 rather than 135842. I need to only parse the numeric value when it is 6 characters or more.

t-sql parsing patindex

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-01-15T08:25:05Z

tlenzmeier answered Jan 15, '20

Figured it out:

LEFT(SUBSTRING([SUBJECT], PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', [SUBJECT]), 8000), PatIndex('%[^0-9]%', SubString([SUBJECT], PatIndex('%[0-9][0-9][0-9][0-9][0-9][0-9]%', [SUBJECT]), 8000) + 'X')-1)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question