SQL Scripts for Microsoft Dynamics GP: Get Numeric Characters from an Alphanumeric String

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

Read original post SQL Scripts for Microsoft Dynamics GP: Get Numeric Characters from an Alphanumeric String at azurecurve|Ramblings of an IT Professional