piątek, 5 grudnia 2008

Czy ten łańcuch znaków jest liczbą?

Problem z pozoru banalny. Sprawdzenie czy łańcuch znaków jest liczbą. Jak to zrobić? Większość osób z pewnością od razu skorzystałaby z funkcji ISNUMERIC. Problem polega na tym, że ta funkcja sprawdza czy dany łańcuch jest typu numerycznego, a nie czy jest liczbą. Funkcja ISNUMERIC zwróci wartość 1 dla następujących wartości '+', '-', '101.5E9', '$10', oraz innych wartości pieniężnych zawierających odpowiednie znaki reprezentujące waluty (http://msdn.microsoft.com/en-us/library/ms188688.aspx).

Jakie mogą być konsekwencje sprawdzania łańcucha znaków tylko za pomocą ISNUMERIC? Ano takie, że funkcja, procedura czy tam inny kod napisany w SQL się wysypie. Poniżej kilka przykładów, w których ISNUMERIC za każdym razem zawraca wartość 1.

DECLARE @STRING AS VARCHAR(1)
SET @STRING = '+'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(DECIMAL,@STRING) + 10
GO


Msg 8115, Level 16, State 6, Line 5
Arithmetic overflow error converting varchar to data type numeric.


DECLARE @STRING AS VARCHAR(3)
SET @STRING = '$10'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(INT, @STRING) + 10
GO


Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '$10' to data type int.


DECLARE @STRING AS VARCHAR(10)
SET @STRING = '101.5E9'
IF ISNUMERIC(@STRING) = 1
SELECT CONVERT(INT, @STRING) + 10
GO


Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '101.5E9' to data type int.


Jak więc sprawdzić czy łańcuch znaków jest wartością numeryczną, oraz jest typu, który pożądany jest w dalszych obliczeniach?

Ano można skorzystać w tym celu np. z wyrażeń regularnych. Jako, że wyrażenia regularne nie występują póki co w SQL Server, potrzebna będzie funkcja napisana w CLR.

W poprzednim poście (
http://updatedev.blogspot.com/2008/11/transformacja-wyniku-zapytania-do.html) zaprezentowałem bibliotekę CLTToolsSuite. Rozbuduję ją teraz o kolejną funkcję, która będzie weryfikowała łańcuch znaków za pomocą wyrażeń regularnych.

<SqlFunction(isdeterministic:=True, dataaccess:=DataAccessKind.None)> _
Public Shared Function RegEx(ByVal input As SqlString, ByVal pattern As SqlString) As SqlBoolean
Dim RegExpr As New Regex(pattern)
Dim match As Match

If input.IsNull Or pattern.IsNull Then
Return False
End If

match = RegExpr.Match(input)
If match.Success Then
Return True
Else
Return False
End If
End Function

Funkcja ta akceptuje dwa parametry. Łańcuch znaków do zweryfikowania oraz szablon wyrażenia regularnego. W przypadku gdy łańcuch znaków jest poprawny funkcja zwraca 1, w każdym innym przypadku 0.

Po skompilowaniu biblioteki pora ja zarejestrować i utworzyć nową funkcję.

IF OBJECT_ID('fn_RegEx') IS NOT NULL
drop function fn_RegEx

IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLRToolsSuite')
drop assembly CLRToolsSuite
go

CREATE ASSEMBLY CLRToolsSuite
from 'C:\CLRToolsSuite\CLRToolsSuite\bin\Release\CLRToolsSuite.dll'
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.fn_RegEx(@input as nvarchar(max), @pattern as nvarchar(max))
RETURNS BIT
EXTERNAL name CLRToolsSuite.[CLRToolsSuite.CLRToolsSuite].RegEx;
GO

I teraz krótki test, w którym funkcja ISNUMERIC została zastąpiona przez nową funkcję fn_RegEx.

DECLARE @STRING AS VARCHAR(5)
SET @STRING = '+'
IF dbo.fn_RegEx(@STRING, '^[0-9]+(\.*[0-9]*)$') = 1
SELECT CONVERT(DECIMAL,@STRING) + 10
GO

DECLARE @STRING AS VARCHAR(5)
SET @STRING = '$10'
IF dbo.fn_RegEx(@STRING, '^[0-9]+$') = 1
SELECT CONVERT(INT,@STRING) + 10
GO

DECLARE @STRING AS VARCHAR(10)
SET @STRING = '101.5E9'
IF dbo.fn_RegEx(@STRING, '^[0-9]+$') = 1
SELECT CONVERT(INT,@STRING) + 10
GO

Jak widać z powyższych fragmentów kodu coś co może na pierwszy rzut oka wydawać się banalne, wcale takim w rzeczywistości być nie musi. Warto pamiętać więc co funkcja ISNUMERIC robi i jakie są jej ograniczenia.

Prześlij komentarz