Day: April 18, 2023

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will return all of the numeric characters from a string; if you have a string of TEMP0001A99 it will return 000199.

-- drop FUNCTION if it exists
IF OBJECT_ID (N'uf_AZRCRV_GetNumber', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetNumber
GO
--CREATE FUNCTION
CREATE FUNCTION uf_AZRCRV_GetNumber(@string NVARCHAR(100))
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
RETURNS NVARCHAR(100)
BEGIN
  RETURN
  (
		SELECT CAST(
			(
				SELECT
					CASE WHEN SUBSTRING(@string, n, 1) LIKE '[0-9]' THEN
						SUBSTRING(@string, n, 1)
					ELSE
						''
					END
				FROM
					(
						SELECT
							number
						FROM
							master..spt_values
						WHERE
							type = 'P' AND number BETWEEN 1 AND 100
					) AS Nums(n)
				WHERE
					n <= LEN(@string) FOR XML PATH('')
			) AS NVARCHAR(100)
		)  
	);
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetNumber TO DYNGRP
GO