SQL query or function to get only numerical values before and after certain word

Question

I have a column in a table that has values like below and I'd like to get the numeric value after the keyword APP and before the next space after APP*. Thanks so much in advance!

Data Service B2B **APP#1234** Rehearsal 03/01/2025
Office 365 **APP 23456** for project 123
Office 365 **APP555** for project 123

Desired output for line 1: 1234
Desired output for line 2: 23456
Desired output for line 3: 555

I am using the following function from this website: https://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/

For #2 (Data Service B2B APP#1234 Rehearsal 03/01/2025) above, it output 2 from B2B as well.

CREATE FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)

    BEGIN
        WHILE @intAlpha > 0 
        BEGIN
            SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
            SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
        END
    END

    RETURN ISNULL(@strAlphaNumeric,0)
END

A scalar function with WHILE would likely be one of the slowest ways to do this. An iTVF would be a far better option. — Thom A
– Thom A ♦, Commented Mar 2 at 22:34
Hi @MartinSmith, SQL 2019. sorry forgot to put in the original question. — Benjamin P
– Benjamin P, Commented Mar 3 at 1:23
23456 is not "before the next space after APP*". Please clarify when and why spaces should be ignored. — MatBailie
– MatBailie, Commented Mar 3 at 7:27

Dale K · Accepted Answer · 2025-03-03 02:17:25Z

4

For a problem like this you want to avoid using a function (unless an iTVF) and certainly avoid using a loop, as neither perform very well.

This might not be the compact way to go about it, but the logic is clearer (IMO). You could move this into a function (iTVF) for reusability.

Find the string "APP" and remove it and all preceding text.
Remove any non-numeric characters immediately following "APP"
Obtain the number using substring from here to the next space.

with TestValues as (
  select *
  from (
    values
    ('Data Service B2B APP#1234 Rehearsal 03/01/2025'),
    ('Office 365 APP 23456 for project 123'),
    ('Office 365 APP555 for project 123')
  ) x (Value)
), cte1 as (
  -- Find "APP" and remove from string
  select Value
    , substring(Value, patindex('%APP%', Value) + 3, len(Value)) NewValue
  from TestValues
), cte2 as (
select Value
  -- Find first numeric value after "APP"
  , substring(NewValue, patindex('%[0-9]%', NewValue), len(NewValue)) NewNewValue
from cte1
)
select Value
  -- Take a string until the next space
  , substring(NewNewValue, 1, patindex('% %',NewNewValue)) Number
from cte2;

Returns

Value	Number
Data Service B2B APP#1234 Rehearsal 03/01/2025	1234
Office 365 APP 23456 for project 123	23456
Office 365 APP555 for project 123	555

fiddle

A tidier solution is (Thanks for the reminder Martin Smith):

select Value, number
from (
    values
    ('Data Service B2B APP#1234 Rehearsal 03/01/2025'),
    ('Office 365 APP 23456 for project 123'),
    ('Office 365 APP555 for project 123')
) TestValues (Value)
cross apply (values(substring(Value, patindex('%APP%', Value) + 3, len(Value)))) ca1 (substring_after_app)
cross apply (values(substring(substring_after_app, patindex('%[0-9]%', substring_after_app), len(substring_after_app)))) ca2 (substring_from_first_digit)
cross apply (values(substring(substring_from_first_digit, 1, patindex('% %',substring_from_first_digit)))) ca3 (number)

To run this against your own table use the following:

select Value, number
from MyTable
cross apply (values(substring(Value, patindex('%APP%', Value) + 3, len(Value)))) ca1 (substring_after_app)
cross apply (values(substring(substring_after_app, patindex('%[0-9]%', substring_after_app), len(substring_after_app)))) ca2 (substring_from_first_digit)
cross apply (values(substring(substring_from_first_digit, 1, patindex('% %',substring_from_first_digit)))) ca3 (number)

edited Mar 3 at 2:17

answered Mar 2 at 22:33

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Benjamin P Mar 3 at 1:29

Hi @DaleK thanks so much. sorry for another beginner question. How would I go about putting that query for an actual column/field name? I tried using my field name from my table and it shows a lot of errors. Thanks again.

Dale K Mar 3 at 2:17

Updated answer.

MatBailie Mar 3 at 7:22

I'd have thought that '%[^0-9]%' would be safer than '% %'?

Dale K Mar 3 at 8:34

Could be, always lots of potential edge cases missed from question. I prefer to meet the needs of the data shown and hopefully they can adapt to their real data.

Benjamin P Mar 3 at 13:14

Thanks so much @DaleK, Martin Smith and everyone else. Greatly appreciate it.

Collectives™ on Stack Overflow

SQL query or function to get only numerical values before and after certain word

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related