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
