[Solved] I’m looking for a way to trim a string so that only 6 consecutive digits show

Question

You’re close! You need to use PATINDEX instead of CHARINDEX.

After that, you can use SUBSTRING to pull the six numbers out.

This should work for you:

Select  SUBSTRING(d.Notes, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', d.Notes), 6) 
From    YourTable d

If there are records in your table that do not have six consecutive numbers, you can use either of the following as well:

To default results to NULL when six consecutive numbers are not found

Select  Case When PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', d.Notes) <> 0 
            Then SUBSTRING(d.Notes, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', d.Notes), 6) 
            Else Null
        End 
From    YourTable d

To not pull back results without six consecutive numbers

Select  SUBSTRING(d.Notes, PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%', d.Notes), 6) 
From    YourTable d
Where   d.Notes LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'

Accepted Answer