[#] Funkcja: isValidString (dozwolone ASCII aA-zZ oraz 0-9) - MSSQL

( Ostatnio zmieniony wt., 03/06/2008 - 13:07 )
 
IF EXISTS
(
  SELECT *
  FROM dbo.sysobjects
  WHERE
    Id = object_id(N'[dbo].[isValidString]')
    AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[isValidString]
GO



CREATE FUNCTION [dbo].[isValidString] (@String nvarchar(255)) RETURNS bit
AS
BEGIN
  DECLARE @position int
  DECLARE @MyOutput bit

  SET @MyOutput = 1
  SET @position = 1

WHILE (@position <= LEN(@String) and @MyOutput = 1)
BEGIN
  IF @MyOutput = 1 and NOT EXISTS
  (
     select 1 where
     (
        unicode(SUBSTRING(@String, @position, 1)) <= 57
        and unicode(SUBSTRING(@String, @position, 1)) >= 48
     )
     or
     (
        unicode(SUBSTRING(@String, @position, 1)) <= 90
        and unicode(SUBSTRING(@String, @position, 1)) >= 65
     )
     or
     (
        unicode(SUBSTRING(@String, @position, 1)) <= 122
        and unicode(SUBSTRING(@String, @position, 1)) >= 97
     )
   )
   BEGIN
    SET @MyOutput = 0
   END

   SET @position = @position + 1
  END

  RETURN @MyOutput
END
GO

Wywołanie

SELECT [database].[dbo].[isValidString]('123aałą#')

Inne przykłady – niekoniecznie dobre :)

CREATE FUNCTION [dbo].[isValidSystemKey] (@SystemKey varchar(255)) RETURNS bit
AS
BEGIN
  DECLARE @MyOutput bit
  DECLARE @LENGTH int

  SET @MyOutput = 1
  SET @LENGTH = LEN(@SystemKey)

  WHILE (@LENGTH > 0 and @MyOutput = 1) BEGIN
    IF
        @MyOutput = 1 and not
    (
        (
          ascii(substring(@SystemKey,@LENGTH,1)) >= 48
          and ascii(substring(@SystemKey,@LENGTH,1)) <= 57
        )
        or
        (
          ascii(substring(@SystemKey,@LENGTH,1)) >= 65
          and ascii(substring(@SystemKey,@LENGTH,1)) <= 90
        )
        or
        (
          ascii(substring(@SystemKey,@LENGTH,1)) >= 97
          and ascii(substring(@SystemKey,@LENGTH,1)) <= 122
        )
     )
     BEGIN
        SET @MyOutput = 0
     END

     SET @LENGTH = @LENGTH - 1
   END

  RETURN @MyOutput
END
CREATE FUNCTION [dbo].[isValidString] (@String varchar(10)) RETURNS bit
AS
BEGIN
   DECLARE @MyOutput bit

   IF EXISTS (
        SELECT
           1
        FROM
        (
          SELECT 1 AS digit UNION all SELECT  2 UNION all
          SELECT 3 UNION all SELECT 4 UNION all
          SELECT 5 UNION all SELECT 6 UNION all
          SELECT 7 UNION all SELECT 8 UNION all
          SELECT 9 UNION all SELECT 10
        ) AS N
        WHERE
          ascii(substring(@String, digit, 1 )) not between 48 and 57
          and ascii(substring(@String, digit, 1 ))  not between 65 and 90
          and ascii(substring(@String, digit, 1 ))  not between 97 and 122
   ) SET @MyOutput = 0
   ELSE
     SET @MyOutput = 1

   RETURN @MyOutput
END

Wywołanie

SELECT [database].[dbo].[isValidString]('123aa')
-- Or to check for unsigned integers alone
--if patindex( '%[^0-9]%' , @c ) > 0
--      print 'No'
--else
--      print 'Yes'
5
Twoja ocena: Brak Średnio: 5 (1 vote)