Extract number from VARCHAR

Question

I have a [Comment]type column VARCHAR(255)in a table from which I am trying to extract numbers from. Numbers will always be 12 digits, but usually are not in the same place. Some of them will also have more than one 12-digit number, this is normal, but I only need the first.

I tried using PATINDEX('%[0-9]%',[Comment]), but I can’t figure out how to set the requirement to 12 digits.

An example of the data I'm working with is below:

Combined 4 items for $73.05 with same claim no. 123456789012 as is exceeding financial limits
Consolidated remaining amount of claim numbers, 123456789013, 123456789014, 123456789015, 123456789016 due to financial limits

Answer 1

You can just use 12 [0-9]in a line:

PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9‌​][0-9][0-9]%',[Comme‌​nt])