sql server - Check SIRET and SIREN (French entreprise number)

Question

Is there a function in T-SQL to control an SIRET or a SIREN? This function use Luhn Algorithm like Here

Answer 1

You can use this :

Method for luhn algo:

CREATE FUNCTION [dbo].[F_CHECK_LUHN] (@DATA VARCHAR(32)) 
RETURNS BIT AS 

BEGIN 
-- valeurs limites 
IF @DATA IS NULL OR @DATA = '' RETURN NULL; 

DECLARE @Cpt INT = 0;
declare @C CHAR(1);
declare @Chiffre integer=0;  
declare @Somme integer=0;
declare @parity integer;
set  @parity=LEN(@DATA) % 2;

WHILE @Cpt < LEN(@DATA)
BEGIN

   SET @C = SUBSTRING(@DATA, @Cpt+1, 1);

    IF @C NOT BETWEEN '0' AND '9' RETURN NULL;

    set @Chiffre=CAST(@C AS SMALLINT);

    if ((@Cpt % 2) =@parity)
    begin
    set @Chiffre=@Chiffre*2;

        if (@Chiffre>9)
        begin
        set @Chiffre=@Chiffre-9;
        end;

    end;

    set @Somme=@Somme+@Chiffre;

    SET @Cpt = @Cpt + 1;
END;

RETURN CASE WHEN @Somme % 10 = 0 THEN 1 ELSE 0 end ;

END;

Function for control SIREN :

CREATE FUNCTION [dbo].[F_IsSiren](@DATA VARCHAR(9)) 
RETURNS BIT AS 

BEGIN 

if @DATA is null or len(@DATA)<>9 or @DATA='000000000' return 0;

DECLARE  @Result bit;

select @Result=dbo.F_CHECK_LUHN(@DATA)

return @Result;
END ;

Function for control SIRET :

CREATE FUNCTION [dbo].[F_IsSiret](@DATA VARCHAR(14)) 
RETURNS BIT AS 

BEGIN 

if @DATA is null or len(@DATA)<>14 or @DATA='00000000000000' return 0;

DECLARE  @Result bit;
DECLARE  @ValidLuhn bit;


set @ValidLuhn=dbo.F_CHECK_LUHN(@DATA);

--Cas particuliere de la poste, la somme des chiffres doit être modulo 5
if @ValidLuhn=0 and left(@DATA, 9)='356000000'
begin
DECLARE @Cpt INT = 0;
declare @C CHAR(1);
declare @Chiffre integer=0;  
declare @Somme integer=0;

WHILE @Cpt < LEN(@DATA)
BEGIN

   SET @C = SUBSTRING(@DATA, @Cpt+1, 1);

    IF @C NOT BETWEEN '0' AND '9' 
    begin
    set @Somme=-1;
    break;
    end

    set @Chiffre=CAST(@C AS SMALLINT);

    set @Somme=@Somme+@Chiffre;

    SET @Cpt = @Cpt + 1;
END;

if ((@Somme % 5) =0)
set @Result=1;
else
set @Result=0;


end 
else
begin
--Verification algo de luhn
select @Result=@ValidLuhn;
end

return @Result;
END ;