sql server - SQL query or function to get only numerical values before and after certain word - Stack Overflow

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: /

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

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/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

• sql
• sql-server
• function
• sql-server-2019

Share Improve this question Follow edited Mar 3 at 4:38 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 2 at 22:16 Benjamin PBenjamin P 1311 silver badge33 bronze badges 4

• 1 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 Commented Mar 2 at 22:34
• Hi @MartinSmith, SQL 2019. sorry fot to put in the original question. – Benjamin P Commented Mar 3 at 1:23
• will do..thanks :) – Benjamin P Commented Mar 3 at 1:35
• 1 23456 is not "before the next space after APP*". Please clarify when and why spaces should be ignored. – MatBailie Commented Mar 3 at 7:27

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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.

1. Find the string "APP" and remove it and all preceding text.
2. Remove any non-numeric characters immediately following "APP"
3. 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)