Search code examples
sqlsql-servert-sqlsql-server-2012patindex

Extract Number from VARCHAR

I have a [Comment] column of type VARCHAR(255) in a table that I'm trying to extract numbers from. The numbers will always be 12 digits, but aren't usually in the same place. Some of them will also have more than one 12 digit number, which is fine, but I only need the first.

I've tried using PATINDEX('%[0-9]%',[Comment]), but I can't figure out how to set a requirement of 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
Solution

  • You can just use 12 [0-9]'s in a row:

    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])