How to extract number that is greter than or equal to 16 digits

[buzza1234]

Good Morning,

I am using sql server 2022, I have a table I would like to extract 16 Digits or more numbers (if any exist)

Please kindly help. Thank you in advance

Create Table #Payments (ID int, Comment Text)

INSERT INTO #payments values (1, '65607  Processed via? DISC  Last 4 digits of credit card:7144  Last 4 digits of checking account:  Confirmation number: 8787082513313224314  Receipt sent? No  CSR: Charlett')
INSERT INTO #payments values (2, 'HO_QuoteOutput_11131_0.5509311349467635_1.pdf     Category: QUOTE     SubCategory: Card is 3412123412341234')
INSERT INTO #payments values (3, '11/28/12 auto rnwl /// paid in full #4482501239835011 x 5/14 paid in full emld receipt')
INSERT INTO #payments values (4, 'Processed CC payment of $514.24 per webiste Confirmation number: 08748C 3482568639165011    Processed check of $64 per feat website Confirmation Number: 9019991201')
INSERT INTO #payments values (5, '2/26 left a message for william.  3295531151201648 x 12/17 121 begin march 1st.  email to isnd  nat gen 25/50 11.65 down and 18.53 monthly.     Natl Gen - 6 mo, $101    Allied - 6 mo, $11.06    Foremost - 6 mo, $15    william: 4dob 1/4/2011, L522-121-59-094-0  2015 Ford Fiesta  2 accident.   POI w/ Direct General paying 181. Assume 11/150/10, quote 30/60')
INSERT INTO #payments values (6, 'need to rit  5010440064321111  11/18  385')

select * from #payments

so expected numbers from above is 16 digits or more (continuous numeric value) spaces ok but no other characters

8787082513313224314
please Ignore any number with *.pdf or *.jpg so it has to get the next 16 digit number if any so here 3412123412341234
4482501239835011
3482568639165011
3295531151201648
5010440064321111

just would like to extract any continuous number that is 16 Digits or more (continuous number) other wise ignore. the first occurance of 16 Digits or more.

Thank you,

Milan

[#2]

Hello @buzza1234 

Try this 

SELECT ID,value
FROM #payments
    CROSS APPLY STRING_SPLIT(String, ' ')
WHERE isnumeric(value) =1 AND LEN (value) >= 16

Regards

Javier

[#3]

@Javier Villegas 

Hi, Javier.

That will handle most of the example data provided but not row 3 (ID = 3), since '#4482501239835011' will fail on ISNUMERIC.

If we knew for a fact that the hash sign was the only character to worry about, we could remove it with a REPLACE prior to the STRING_SPLIT and we'd be fine, but since it could be any character, this really lends itself to a CLR approach and bringing in a proper .NET RegEx class (which in turn only lends itself to SQL Server and Azure SQL MI) and presenting that as a scalar function (since only the first match is asked for).

Cheers,

Lain