CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012patindex
JR00K
JR00K

Reputation: 55

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

Upvotes: 4

Views: 173

Answers (1)

Hart CO
Hart CO

Reputation: 34784

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

Upvotes: 6

Related Questions