Archiwum kategorii ‘Bazy danych’

Walidacja REGON dla MySQL

piątek, 27 Sierpień 2010 

CREATE FUNCTION `valid_regon`(`regon` varchar(255)) RETURNS int(1)
    READS SQL DATA
BEGIN

DECLARE cs integer DEFAULT 0;
DECLARE m integer  DEFAULT 0;
DECLARE i integer DEFAULT 0;

IF (char_length(regon) = 7) THEN

       simple_loop: LOOP
         SET i=i+1;
                        if i in (1) then
                                SET m = 2;
                        elseif i in (2) then
                                SET m = 3;
                        elseif i in (3) then
                                SET m = 4;
                        elseif i in (4) then
                                SET m = 5;
                        elseif i in (5) then
                                SET m = 6;
                        elseif i in (6) then
                                SET m = 7;
                        end if;
			SET cs = cs + (m * substr(regon,i,1));

         IF i=6 THEN
            LEAVE simple_loop;
         END IF;
       END LOOP simple_loop;

                SET cs = cs % 11;
                if ( cs = substr(regon,7,1) ) then
                        return 1;
                else
                        return 0;
                end if;

ELSEIF (char_length(regon) = 9) THEN

       simple_loop: LOOP
         SET i=i+1;
                        if i in (3) then
                                SET m = 2;
                        elseif i in (4) then
                                SET m = 3;
                        elseif i in (5) then
                                SET m = 4;
                        elseif i in (6) then
                                SET m = 5;
                        elseif i in (7) then
                                SET m = 6;
                        elseif i in (8) then
                                SET m = 7;
                        elseif i in (1) then
                                SET m = 8;
                        elseif i in (2) then
                                SET m = 9;
                        end if;
			SET cs = cs + (m * substr(regon,i,1));

         IF i=8 THEN
            LEAVE simple_loop;
         END IF;
       END LOOP simple_loop;

                SET cs = cs % 11;
                if ( cs = substr(regon,9,1) ) then
                        return 1;
                else
                        return 0;
                end if;	

ELSEIF (char_length(regon) = 14) THEN

       simple_loop: LOOP
         SET i=i+1;
                        if i in (1,11) then
                                SET m = 2;
                        elseif i in (8) then
                                SET m = 3;
                        elseif i in (2,12) then
                                SET m = 4;
                        elseif i in (4) then
                                SET m = 5;
                        elseif i in (9) then
                                SET m = 6;
                        elseif i in (7) then
                                SET m = 7;
                        elseif i in (3,13) then
                                SET m = 8;
                        elseif i in (6) then
                                SET m = 9;
                        elseif i in (5) then
                                SET m = 0;
                        end if;
			SET cs = cs + (m * substr(regon,i,1));

         IF i=13 THEN
            LEAVE simple_loop;
         END IF;
       END LOOP simple_loop;

                SET cs = cs % 11;
                if ( cs = substr(regon,14,1) ) then
                        return 1;
                else
                        return 0;
                end if;
ELSE
	return -1;
END IF;

END

Zaszufladkowany do Bazy danych | Brak komentarzy »

Walidacja PESEL dla MySQL

piątek, 27 Sierpień 2010 

CREATE FUNCTION `valid_pesel`(`pesel` varchar(255)) RETURNS int(1)
    READS SQL DATA
BEGIN

DECLARE cs integer DEFAULT 0;
DECLARE m integer  DEFAULT 0;
DECLARE i integer DEFAULT 0;

IF (char_length(pesel) = 11) THEN

       simple_loop: LOOP
         SET i=i+1;
                        if i in (1,5,9) then
                                SET m = 1;
                        elseif i in (2,6,10) then
                                SET m = 3;
                        elseif i in (3,7) then
                                SET m = 7;
                        elseif i in (4,8) then
                                SET m = 9;
                        end if;
			SET cs = cs + (m * substr(pesel,i,1));

         IF i=10 THEN
            LEAVE simple_loop;
         END IF;
       END LOOP simple_loop;

                SET cs = cs % 10;

                if (cs > 0) then
                        SET cs = 10 - cs;
                end if;

                if ( cs = substr(pesel,11,1) ) then
                        return 1;
                else
                        return 0;
                end if;

ELSE
	return -1;
END IF;

END

Zaszufladkowany do Bazy danych | Brak komentarzy »

Walidacja NIP dla MySQL

piątek, 27 Sierpień 2010 
CREATE FUNCTION `valid_nip`(`nip` varchar(255)) RETURNS int(1)
    READS SQL DATA
BEGIN

DECLARE cs integer DEFAULT 0;
DECLARE m integer  DEFAULT 0;
DECLARE i integer DEFAULT 0;

IF (char_length(nip) = 10) THEN

       simple_loop: LOOP
         SET i=i+1;
                        if i in (4) then
                                SET m = 2;
                        elseif i in (5) then
                                SET m = 3;
                        elseif i in (6) then
                               SET m = 4;
                        elseif i in (2,7) then
                                SET m = 5;
                        elseif i in (1,8) then
                               SET m = 6;
                        elseif i in (3,9) then
                                SET m = 7;
                        end if;
			SET cs = cs + (m * substr(nip,i,1));

         IF i=9 THEN
            LEAVE simple_loop;
         END IF;
       END LOOP simple_loop;

                SET cs = cs % 11;
                if ( cs = substr(nip,10,1) ) then
                        return 1;
                else
                        return 0;
                end if;

ELSE
	return -1;
END IF;

END

Zaszufladkowany do Bazy danych | Brak komentarzy »

Walidacja PESEL dla MSSQL

piątek, 27 Sierpień 2010 

CREATE FUNCTION valid_pesel( @pesel nvarchar(255) ) RETURNS INTEGER
AS
BEGIN
	DECLARE @cs int
	DECLARE @m int
	DECLARE @i int
	DECLARE @res int
	SET @pesel = cast(@pesel as nvarchar)
	SET @cs = 0
	SET @m = 0
	SET @i = 1
	IF LEN(@pesel) = 11
		begin
			WHILE (@i <= 10)
			BEGIN
                  if @i in (1,5,9) SET @m = 1
                  else if @i in (2,6,10) SET @m = 3
                  else if @i in (3,7) SET @m = 7
                  else if @i in (4,8) SET @m = 9
				  SET @cs = @cs + (@m * SUBSTRING(@pesel,@i,1))
				  SET @i = (@i + 1)
			END
			SET @cs = @cs % 10
            if (@cs > 0) SET @cs = 10 - @cs
			IF ( @cs = SUBSTRING(@pesel,11,1) )
				SET @res = 1
			ELSE
				SET @res = 0
		END
	ELSE
		BEGIN
			SET @res = -1
		END
	RETURN @res
END

Zaszufladkowany do Bazy danych | Brak komentarzy »

Walidacja REGON dla MSSQL

piątek, 27 Sierpień 2010 
CREATE FUNCTION valid_regon( @regon nvarchar(255) ) RETURNS INTEGER
AS
BEGIN
	DECLARE @cs int
	DECLARE @m int
	DECLARE @i int
	DECLARE @res int
	SET @regon = cast(@regon as nvarchar)
	SET @cs = 0
	SET @m = 0
	SET @i = 1
	IF LEN(@regon) = 7
		BEGIN
			WHILE (@i <= 6)
			BEGIN
                  IF @i IN (1) SET @m = 2
                  ELSE IN @i in (2) SET @m = 3
                  ELSE IN @i in (3) SET @m = 4
                  ELSE IN @i in (4) SET @m = 5
                  ELSE IN @i in (5) SET @m = 6
                  ELSE IN @i in (6) SET @m = 7
				  SET @cs = @cs + (@m * SUBSTRING(@regon,@i,1))
				  SET @i = (@i + 1)
			END
			SET @cs = @cs % 11
			IF ( @cs = SUBSTRING(@regon,7,1) )
				SET @res = 1
			ELSE
				SET @res = 0
		END
	ELSE IF LEN(@regon) = 9
		BEGIN
			WHILE (@i <= 8)
			BEGIN
                  IF @i IN (3) SET @m = 2
                  ELSE IF @i IN (4) SET @m = 3
                  ELSE IF @i IN (5) SET @m = 4
                  ELSE IF @i IN (6) SET @m = 5
                  ELSE IF @i IN (7) SET @m = 6
                  ELSE IF @i IN (8) SET @m = 7
                  ELSE IF @i IN (1) SET @m = 8
                  ELSE IF @i IN (2) SET @m = 9
				  SET @cs = @cs + (@m * SUBSTRING(@regon,@i,1))
				  SET @i = (@i + 1)
			END
			SET @cs = @cs % 11
			IF ( @cs = SUBSTRING(@regon,9,1) )
				SET @res = 1
			ELSE
				SET @res = 0
		END
	ELSE IF LEN(@regon) = 14
		BEGIN
			WHILE (@i <= 13)
			BEGIN
                  IF @i IN (1,11) SET @m = 2
                  ELSE IF @i IN (8) SET @m = 3
                  ELSE IF @i IN (2,12) SET @m = 4
                  ELSE IF @i IN (4) SET @m = 5
                  ELSE IF @i IN (9) SET @m = 6
                  ELSE IF @i IN (7) SET @m = 7
                  ELSE IF @i IN (3,13) SET @m = 8
                  ELSE IF @i IN (6) SET @m = 9
                  ELSE IF @i IN (5) SET @m = 0
				  SET @cs = @cs + (@m * SUBSTRING(@regon,@i,1))
				  SET @i = (@i + 1)
			END
			SET @cs = @cs % 11
			IF ( @cs = SUBSTRING(@regon,14,1) )
				SET @res = 1
			ELSE
				SET @res = 0
		END
	ELSE
		BEGIN
			SET @res = -1
		END
	RETURN @res
END

Zaszufladkowany do Bazy danych | Brak komentarzy »

Walidacja NIP dla MSSQL

piątek, 27 Sierpień 2010 
CREATE FUNCTION valid_nip( @nip nvarchar(255) ) RETURNS INTEGER
AS
BEGIN
 DECLARE @cs int
 DECLARE @m int
 DECLARE @i int
 DECLARE @res int
 SET @nip = cast(@nip as nvarchar)
 SET @cs = 0
 SET @m = 0
 SET @i = 1
 IF LEN(@nip) = 10
 begin
 WHILE (@i &lt;= 9)
 BEGIN
 if @i in (4) SET @m = 2
 else if @i in (5) SET @m = 3
 else if @i in (6) SET @m = 4
 else if @i in (2,7) SET @m = 5
 else if @i in (1,8) SET @m = 6
 else if @i in (3,9) SET @m = 7
 SET @cs = @cs + (@m * SUBSTRING(@nip,@i,1))
 SET @i = (@i + 1)
 END
 SET @cs = @cs % 11
 IF ( @cs = SUBSTRING(@nip,10,1) )
 SET @res = 1
 ELSE
 SET @res = 0
 END
 ELSE
 BEGIN
 SET @res = -1
 END
 RETURN @res
END

Zaszufladkowany do Bazy danych | Brak komentarzy »