CODEWITHSUNDEEP
sqlsql-servert-sqlsubstringsql-server-2014
N101Seggwaye
N101Seggwaye

Reputation: 75

Extract consecutive integers from a record

I am trying to extract numbers from records that have 4 or more consecutive integers to be used as a reference for another table.

So far I've tried using PATINDEX but it doesn't really work the way I need it to work. If there are records that contain more than 1 set of sequential numbers then I need it to only extract the first one.

What I've tried:

SELECT SUBSTRING(nvt.AdditionalInformation, PATINDEX('%[0-9]%', nvt.AdditionalInformation), LEN(nvt.AdditionalInformation))
FROM dbo.NSReportVtest nvt;

SELECT PATINDEX('%[0-9]%', nvt.AdditionalInformation) AddtionalInformation
FROM dbo.NSReportVersionsTest nvt

Data in table:

Column 1
Added the COB 1.1 & COB 5 Learning types to the report.
Added the previous agreement year and previous agreement final score fields to the report.
Demo Certificate TP35356
TP45905, TP46379, TP44804, TP46432 - Added HasTalentAssessment, AssessmentDate, AssessmentCompleted, PMScore, ValueSurveyScore, OverallPMSCore, Drivers Licence
TP38298 - Removed the Sales Support and Customer Service Consultant - CIC job titles from the report.

Expected results:

Column 2
35356
45905
38298

Upvotes: 0

Views: 279

Answers (1)

Dale K
Dale K

Reputation: 27451

The following appears to accomplish what you want. Find the first set of matching numbers, then find the first non-number char and sub-string between them.

declare @Test table (AdditionalInformation nvarchar(max));

insert into @Test (AdditionalInformation)
values
('Added the COB 1.1 & COB 5 Learning types to the report.'),
('Added the previous agreement year and previous agreement final score fields to the report.'),
('Demo Certificate TP35356'),
('TP45905, TP46379, TP44804, TP46432 - Added HasTalentAssessment, AssessmentDate, AssessmentCompleted, PMScore, ValueSurveyScore, OverallPMSCore, Drivers Licence'),
('TP38298 - Removed the Sales Support and Customer Service Consultant - CIC job titles from the report.');

select T.Original
  -- If we are expecting digits then substring using the 2 calculated positions
  , case when M.FirstMatch > 0 then substring(AdditionalInformation, M.FirstMatch-1, N.SecondMatch-1) else null end
from (
  select T.AdditionalInformation Original
    -- Add a char before and after to make it easier to find the number if its first or last
    , ':' + T.AdditionalInformation + ':' AdditionalInformation
  from @Test T
) T
-- Find the first set of at least 4 digits
cross apply (values (PATINDEX('%[0-9][0-9][0-9][0-9]%', ':'+T.AdditionalInformation+':'))) M (FirstMatch)
-- Find the first non-digit after the 4 digit block starts
cross apply (values (PATINDEX('%[^0-9]%', substring(AdditionalInformation, M.FirstMatch-1, len(AdditionalInformation))))) N (SecondMatch);

Returns:

Original Matching Number
Added the COB 1.1 & COB 5 Learning types to the report. NULL
Added the previous agreement year and previous agreement final score fields to the report. NULL
Demo Certificate TP35356 35356
TP45905, TP46379, TP44804, TP46432 - Added HasTalentAssessment, AssessmentDate, AssessmentCompleted, PMScore, ValueSurveyScore, OverallPMSCore, Drivers Licence 45905
TP38298 - Removed the Sales Support and Customer Service Consultant - CIC job titles from the report. 38298

Upvotes: 2

Related Questions