CODEWITHSUNDEEP
sqlsql-serverfunctionsql-server-2019
Benjamin P
Benjamin P

Reputation: 13

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

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

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

Upvotes: 1

Views: 80

Answers (1)

Dale K
Dale K

Reputation: 27379

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)

Upvotes: 3

Related Questions