SQL Extract Numbers from a String

There are times when you want to create a numeric value from a string to convert descriptive text to a number.  An example is a MAX Sales Order or Quote document created in Synergy that has the Quote or Sales Order Number in the Description, but you want to link back to the MAX Order Master.

One way to do this create an SQL Function that converts strings to numbers.

In SQL Management Studio, execute the following:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[udf_ExtractInteger](@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
    AS
    BEGIN
        DECLARE @count int
        DECLARE @intNumbers VARCHAR(MAX)
        SET @count = 0
        SET @intNumbers = ”

        WHILE @count <= LEN(@string)
        BEGIN
            IF SUBSTRING(@string, @count, 1)>=’0′ and SUBSTRING (@string, @count, 1) <=’9′
                BEGIN
                    SET @intNumbers = @intNumbers + SUBSTRING (@string, @count, 1)
                END
            SET @count = @count + 1
        END
        RETURN @intNumbers
    END

This creates a function named udf_ExtractInteger in the MASTER database.

Then can use this in SQL code to SELECT the numbers in a string with code like this:

SELECT HID, Description, master.dbo.udf_ExtractInteger(Description) AS OrderNumber
FROM BacoDiscussions
WHERE   (dbo.BacoDiscussions.Type = 13) OR             — Quote
                (dbo.BacoDiscussions.Type = 11) OR             — Sales Order
                (dbo.BacoDiscussions.Type = 100000133)     — Invoice   

PlusPoint Bullet