use [--name--]
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS OFF
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO

-- Drop it if it already exists
IF OBJECT_ID('dbo.udfDAL_ExtractNumberFromIonName') IS NOT NULL
BEGIN
	DROP FUNCTION dbo.udfDAL_ExtractNumberFromIonName
END
GO

/******************************************************************************
	Replaces a number in a string with {0}, so that be later on localized then re-formatted
	e.g 'I/O-S48 Cnt' gets stored in the database as 'Channel {0}|48'
	The UI clients localizes 'Channel {0}' to 'Canal {0}', then displays as 'Canal 48'
******************************************************************************/

CREATE FUNCTION [dbo].[udfDAL_ExtractNumberFromIonName]
(
	@text NVARCHAR(255)
)
RETURNS NVARCHAR(255) AS
BEGIN
	DECLARE @posEnd INT, @i INT
	DECLARE @char NVARCHAR(1), @charAfter NVARCHAR(1)
	DECLARE @numberOfDigits INT
	
	SET @posEnd = LEN(@text)
	SET @i = @posEnd
	WHILE (@i >= 0)
	BEGIN
		SET @char = SUBSTRING(@text, @i, 1)

		IF @char >= '0' AND @char <= '9'
		BEGIN
			-- make sure there is a space after the number so that names like 'V (+/-15%)-10m' are untouched
			IF (@posEnd = -1 AND @charAfter = ' ') SET @posEnd = @i
		END
		ELSE IF (@posEnd != -1)
		BEGIN
		SET @numberOfDigits = @posEnd - @i
			-- do not unformat names like '4-30 Disturbnce' or 1/2 Mtr Current or MU Ia^2 or containing EN50160
			IF (@numberOfDigits != 0 AND @numberOfDigits < 4 AND @char != '-' AND @char != '_' AND @char != '/' AND @char != '^')
			BEGIN
				IF (@numberOfDigits = 1 AND @charAfter <= '5')
				BEGIN
					-- do not unformat names containing V1-4, I1-5 or CL1 Day
					IF (@charAfter = '1' AND @char != ' ') BREAK;
					IF (@char = 'V' OR @char = 'I') BREAK;
				END
				RETURN LEFT(@text, @i) + '{0}' + RIGHT(@text, LEN(@text) - @posEnd) + '|' + SUBSTRING(@text, @i + 1, @numberOfDigits)
			END
		SET @posEnd = -1
		END
		SET @i = @i - 1
		SET @charAfter = @char
	END

	RETURN @text
END
GO
