Order references can be buried inside a subject line or a support note. SQL Server 2025 provides REGEXP_SUBSTR and related functions to extract those values with an explicit pattern.
Define the Text Contract Before the Pattern
Decide which identifiers are valid before writing the expression. Specify prefixes, permitted characters, minimum and maximum lengths, boundaries, and whether case matters. A pattern that finds digits everywhere can extract an unrelated date or phone fragment instead of the intended order reference.
These functions are SQL Server 2025 features. Check the installed engine before using them. The separate REGEXP_LIKE and REGEXP_SPLIT_TO_TABLE functions have a compatibility-level 170 requirement; the scalar examples here use the documented extraction and position functions. Do not replace a version check with an assumption based on the query editor's appearance.
I write a small set of accepted and rejected inputs first. It forces the pattern to express the business rule rather than merely succeed on one convenient sentence. Free text is very cooperative when it contains exactly the example you expected, and much less considerate afterward.
Extract the First Number With REGEXP_SUBSTR
The following expression returns the first sequence of ASCII digits from a synthetic message. The pattern intentionally chooses digits zero through nine. It does not claim to accept every writing system's numeric characters.
DECLARE @Text nvarchar(200)=N'Package 1042 is ready, reference 2048.';
SELECT REGEXP_SUBSTR(@Text,N'[0-9]+') AS FirstNumber,
REGEXP_INSTR(@Text,N'[0-9]+') AS FirstNumberPosition,
REGEXP_COUNT(@Text,N'[0-9]+') AS NumericMatches;The extracted value is text. Convert it to an integer only when the identifier contract requires a number and its range has been validated. An order code can contain leading zeros that carry meaning. Converting the extracted string blindly can remove those zeros or fail on a large identifier.
A numeric sequence is a useful starting example, but a production extractor should use the accepted context. Prefixes and boundaries reduce unrelated matches. Decide whether multiple valid references are accepted, rejected, or returned as separate results rather than silently taking whichever happens to appear first.
Pick an Occurrence and Capture Group in REGEXP_SUBSTR
The occurrence argument chooses the requested match. Parentheses define a capture group, and the group argument selects that part rather than the complete matched text. The example keeps the order prefix in the full match but extracts only its digit group separately.
DECLARE @Subject nvarchar(200)=N'Order ORD-1042 ready; follow-up ORD-2048.';
SELECT REGEXP_SUBSTR(@Subject,N'ORD-([0-9]+)',1,1,'c') AS FirstCode,
REGEXP_SUBSTR(@Subject,N'ORD-([0-9]+)',1,2,'c') AS SecondCode,
REGEXP_SUBSTR(@Subject,N'ORD-([0-9]+)',1,1,'c',1) AS FirstDigits,
REGEXP_COUNT(@Subject,N'ORD-([0-9]+)',1,'c') AS CodeMatches;The start argument uses a one-based starting position. The explicit case-sensitive flag avoids leaving case behavior implicit. Pass the flags as a varchar literal such as 'c', because an nvarchar value like N'c' stops the query with error 8116. Use the supported flags that match the contract, and test any case-insensitive requirement deliberately. Pattern matching has its own rules and should not be assumed to inherit every collation behavior of an ordinary SQL comparison.
For REGEXP_SUBSTR, requesting a match that does not exist returns NULL. That absence is an extraction outcome, not necessarily a malformed source record. Apply the business rule afterward: a required reference can make it an exception, while an optional note can legitimately contain none.
Locate a Match Without Extracting Its Value
REGEXP_INSTR provides the match position and can return its ending position. This lets a caller identify a span. The return-option argument precedes the flags argument, so its signature differs from the substring function.
DECLARE @Subject nvarchar(200)=N'Order ORD-1042 ready; follow-up ORD-2048.';
SELECT REGEXP_INSTR(@Subject,N'ORD-([0-9]+)',1,1,0,'c') AS FirstStart,
REGEXP_INSTR(@Subject,N'ORD-([0-9]+)',1,1,1,'c') AS FirstEnd,
REGEXP_INSTR(@Subject,N'ORD-([0-9]+)',1,2,0,'c',1) AS SecondDigitsStart;A zero position means no match. Check that result before using the position in another expression. Keep position semantics consistent with the destination string operation, especially when text contains supplementary Unicode characters. Do not combine byte offsets from another system with these string positions without an explicit conversion contract.
Position output can support highlighting or a structured extraction record, but it does not explain whether the identifier is valid in the database. Validate the captured reference against the authoritative key or approved lookup afterward. Syntax acceptance and business existence are separate checks.
Test REGEXP_SUBSTR on Boundaries and Ambiguous Inputs
The sample table includes a valid code, two codes, a prefix embedded in another word, and no code. Word boundaries make the intended prefix less likely to match an unrelated longer token. They still need testing against the actual punctuation and character rules.
DECLARE @Subjects TABLE(SubjectID int PRIMARY KEY,SubjectText nvarchar(200));
INSERT @Subjects VALUES(1,N'Order ORD-1042 ready'),
(2,N'ORD-1042 and ORD-2048'),
(3,N'XORD-1042 is not an order token'),
(4,N'No reference supplied');
SELECT SubjectID,SubjectText,
REGEXP_SUBSTR(SubjectText,N'\bORD-([0-9]+)\b',1,1,'c') AS CandidateCode,
REGEXP_COUNT(SubjectText,N'\bORD-([0-9]+)\b',1,'c') AS CandidateCount
FROM @Subjects;A count greater than one can require explicit ambiguity handling. Extend the test set with accepted maximum lengths, leading zeros, lowercase input, separators, and malformed prefixes. Keep original text for investigation rather than replacing it with the first candidate and discarding the evidence.
Which unrelated number is most likely to appear beside your target identifier? Add that case before deploying the extractor. I keep expected extraction and rejection outcomes with the pattern so future edits can be reviewed against a stable contract. A pattern that becomes broader can quietly change which source text is accepted.
Compare the Older Positional Approach
Before these functions, PATINDEX and SUBSTRING could locate and slice a simple digit sequence. The following method finds the first digit and stops at the first following non-digit by adding a sentinel. It deliberately handles the no-digit case.
DECLARE @Text nvarchar(200)=N'Package 1042 is ready.';
DECLARE @Start int=PATINDEX(N'%[0-9]%',@Text);
DECLARE @Tail nvarchar(201)=CASE WHEN @Start>0 THEN SUBSTRING(@Text,@Start,200) END;
SELECT CASE WHEN @Start=0 THEN NULL
ELSE SUBSTRING(@Tail,1,PATINDEX(N'%[^0-9]%',@Tail+N'X')-1)
END AS FirstNumber;That approach remains useful for a small fixed extraction on earlier versions. More complex occurrences and capture groups require additional positional logic. The newer functions make those requirements more direct, but a complex regular expression still needs careful tests. Readability improves when the chosen method matches the actual extraction problem.
Deploy the Pattern With Visible Exceptions
Keep a clear exception result for invalid required tokens. Rejecting an ambiguous reference is safer than silently selecting a different order than the sender intended. The accepted extraction should remain explainable from the retained original input and the reviewed pattern version.
Keep the pattern, flags, required occurrence, and expected capture group together in the reviewed module. Log counts and rejected contract cases with appropriate redaction. Measure the cost on representative text volume rather than assuming extraction is free because the expression fits on one line.
REGEXP_SUBSTR is most useful when it expresses a clear and tested text rule. Preserve the original input, distinguish absence from ambiguity, and validate extracted identifiers against their business meaning. The function can find a token; the application still decides what that token permits.
Related reading on this blog: How to Extract Alphanumeric Only From A String? Interview Question of the Week #214 and UDF: User Defined Function to Extract Only Numbers From String: Number Table Method.
A matched token is not an accepted business reference, it is text that still needs a defined validation rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
