Search code examples
sqlsql-servert-sqlsubstringsql-server-2017

SQL Server Substring variable length - numerals of varying length with no consistent pattern

I've checked the many threads here related to variable-length Substrings - none have answered my specific case - please don't mark as possible duplicate unless I somehow missed it.

Azure SQL Server 2017

I have a varchar(50) field called Name in a table dbo.MyTable with these kinds of values:

1143_RRF-tansTracks
DGGP-45-HAEJTJ_wer
3TTKH_YPreTTR
4534554PostRTE
WERET677ITD
PINT_pilly-er_45321

I am attempting to use SUBSTRING to get this:

1143
45
3
4534554
677
45321

I am having trouble understanding how to define the length parameter.

What I have so far:

SUBSTRING(Name, (PATINDEX('%[0-9]%',[Name])),7) AS Number

The 7 is just there as a placeholder - how would I properly write the length parameter to achieve my number extraction?

Solution

  • Not so nice, but if you really need a one-liner:

    SUBSTRING(SUBSTRING(Name, PATINDEX('%[0-9]%',Name),999),1,PATINDEX('%[^0-9]%',SUBSTRING(Name,PATINDEX('%[0-9]%',Name),999)+'x')-1)

    Or:

    SUBSTRING(Name,PATINDEX('%[0-9]%',Name),LEN(NAME)-PATINDEX('%[0-9]%',REVERSE(Name))-PATINDEX('%[0-9]%',Name)+2)