question

tlenzmeier avatar image
tlenzmeier asked

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-sqlparsingpatindex
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
tlenzmeier avatar image
tlenzmeier answered

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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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