- set ANSI_NULLS ON
- set QUOTED_IDENTIFIER ON
- GO
- ---------------------------------------------------------------
-
- CREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC] (@NOMDIV nvarchar(30))
- RETURNS smallint
-
- AS
-
- BEGIN
-
- DECLARE @nom nvarchar(30)
- DECLARE @i smallint
- DECLARE @j smallint
- SET @i = 1
- SET @j = 0
-
- WHILE @i < len(@nomdiv)
- BEGIN
- DECLARE @car nvarchar(1)
- SET @car = (SUBSTRING(@NOMDIV, @i, @i) )
- IF @car IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
- BEGIN
- SET @j = @i
- BREAK
- END
- ELSE
- SET @i = @i + 1
- END
-
- RETURN @j
- END
- ---------------------------------------------------------------
- -- Une seconde solution fournie par Christian plus performante
-
- CREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC_V2] (@NOMDIV nvarchar(30))
- RETURNS smallint
-
- AS
-
- BEGIN
-
- RETURN PATINDEX ('%[0123456789]%', @NOMDIV)
-
- END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
---------------------------------------------------------------
CREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC] (@NOMDIV nvarchar(30))
RETURNS smallint
AS
BEGIN
DECLARE @nom nvarchar(30)
DECLARE @i smallint
DECLARE @j smallint
SET @i = 1
SET @j = 0
WHILE @i < len(@nomdiv)
BEGIN
DECLARE @car nvarchar(1)
SET @car = (SUBSTRING(@NOMDIV, @i, @i) )
IF @car IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
BEGIN
SET @j = @i
BREAK
END
ELSE
SET @i = @i + 1
END
RETURN @j
END
---------------------------------------------------------------
-- Une seconde solution fournie par Christian plus performante
CREATE FUNCTION [dbo].[FN_CHARINDEX_FIRST_NUMERIC_V2] (@NOMDIV nvarchar(30))
RETURNS smallint
AS
BEGIN
RETURN PATINDEX ('%[0123456789]%', @NOMDIV)
END