How to validate the ICCID number of a SIM card in TSQL?

Question

I have been wondering if there is the possibility of validating that big number that is the unique code for every SIM card on the planet, the ICCID.

If there is, does anyone have the code to check if it is valid on a T-SQL function?

I would like to do it without the need of any programming language out of the database.

---

I found this code on C# but I am unnable to convert it to SQL because I just suck at math and c# and this << operator just ended me!

private bool IccidIsValid(string iccid)
{
    try
    {
        int numberStringLength = 18;

        int cs = 0;
        int dodd;
        for (int i = 0; i < numberStringLength; i += 2)
        {
            dodd = Convert.ToInt32(iccid.Substring(i + 1, 1)) << 1;
            cs += Convert.ToInt32(iccid.Substring(i, 1)) + (int)(dodd / 10) + (dodd % 10);
        }
        cs = (10-(cs % 10)) % 10;

        if (cs == Convert.ToInt32(iccid.Substring(numberStringLength, 1)))
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ex)
    {
        return false;
    }
}

Answer 1

I Found it!

CREATE FUNCTION dbo.usp_ValidateICCID ( @inputString VARCHAR(20) )
RETURNS TINYINT 
AS BEGIN 

DECLARE @result TINYINT

IF @inputString NOT LIKE ('%[0-9]%[0-9]%[0-9]%') 
 RETURN 2

DECLARE @charTable TABLE ( 
 Position INT NOT NULL, 
 ThisChar CHAR(1) NOT NULL, 
 Doubled TINYINT, 
 Summed TINYINT ) 

SET @inputString = CAST(@inputString AS CHAR(20))
INSERT INTO @charTable(Position, ThisChar) 
 SELECT 1, SUBSTRING(@inputString, 1, 1) UNION ALL 
 SELECT 2, SUBSTRING(@inputString, 2, 1) UNION ALL 
 SELECT 3, SUBSTRING(@inputString, 3, 1) UNION ALL 
 SELECT 4, SUBSTRING(@inputString, 4, 1) UNION ALL 
 SELECT 5, SUBSTRING(@inputString, 5, 1) UNION ALL 
 SELECT 6, SUBSTRING(@inputString, 6, 1) UNION ALL 
 SELECT 7, SUBSTRING(@inputString, 7, 1) UNION ALL 
 SELECT 8, SUBSTRING(@inputString, 8, 1) UNION ALL 
 SELECT 9, SUBSTRING(@inputString, 9, 1) UNION ALL 
 SELECT 10, SUBSTRING(@inputString, 10, 1) UNION ALL 
 SELECT 11, SUBSTRING(@inputString, 11, 1) UNION ALL 
 SELECT 12, SUBSTRING(@inputString, 12, 1) UNION ALL 
 SELECT 13, SUBSTRING(@inputString, 13, 1) UNION ALL 
 SELECT 14, SUBSTRING(@inputString, 14, 1) UNION ALL 
 SELECT 15, SUBSTRING(@inputString, 15, 1) UNION ALL 
 SELECT 16, SUBSTRING(@inputString, 16, 1) UNION ALL 
 SELECT 17, SUBSTRING(@inputString, 17, 1) UNION ALL 
 SELECT 18, SUBSTRING(@inputString, 18, 1) UNION ALL 
 SELECT 19, SUBSTRING(@inputString, 19, 1) UNION ALL 
 SELECT 20, SUBSTRING(@inputString, 20, 1)

DELETE FROM @charTable
WHERE  ThisChar NOT LIKE('[0-9]') 

DECLARE @tempTable TABLE ( 
 NewPosition INT IDENTITY(1,1), 
 OldPosition INT ) 
INSERT INTO @tempTable (OldPosition)
 SELECT Position 
 FROM @charTable 
 ORDER BY Position ASC 

UPDATE  @charTable
SET   Position = t2.NewPosition 
FROM  @charTable t1 
INNER JOIN  @tempTable t2 ON t1.Position = t2.OldPosition 

IF ( SELECT MAX(Position) % 2 FROM @charTable ) = 0 
BEGIN 
 UPDATE @charTable
 SET  Doubled = CAST(ThisChar AS TINYINT) * 2 
 WHERE Position % 2 <> 0 
END
ELSE BEGIN 
 UPDATE @charTable 
 SET  Doubled = CAST(ThisChar AS TINYINT) * 2 
 WHERE Position % 2 = 0 
END 


UPDATE @charTable
SET  Summed = 
   CASE WHEN Doubled IS NULL 
     THEN CAST(ThisChar AS TINYINT) 
     WHEN Doubled IS NOT NULL AND Doubled <= 9 
     THEN Doubled 
     WHEN Doubled IS NOT NULL AND Doubled >= 10 
     THEN (Doubled / 10) + (Doubled - 10) 
   END      


IF ( SELECT SUM(Summed) % 10 FROM @charTable ) = 0
 SET @result = 1
ELSE 
 SET @result = 0

RETURN @result 

END

---

I just remade the Internet version to make it faster without the need of a temporary table:

alter function dbo.usp_ValidaICCID(@inputStr varchar(20))
    RETURNS tinyint
as begin
  declare @workStr   varchar(20)
  declare @strLength smallint
  declare @i         smallint
  declare @sum       smallint
  declare @prod      smallint
  declare @digit     char(1)
  declare @evenInd   tinyint
  declare @result    tinyint
  if(@inputStr not LIKE ('%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]%[0-9]'))
        return 2
  set @strLength = len ( @inputStr )
  set @i = 1
  set  @workStr = ''
  WHILE @i <= @strLength
  BEGIN
    set @digit = SUBSTRING(@inputStr, @i, 1)
    if(@digit LIKE '[0-9]') set @workStr = @workStr + @digit
    set @i = @i +1
  end
  set @sum     = 0
  set @evenInd = 0
  if(@strLength % 2 = 0)
     set @evenInd = 1
  set @i = 1
  WHILE @i <= @strLength
  BEGIN
    set @digit = SUBSTRING(@workStr, @i, 1)
    set @prod   = CONVERT(smallint , @digit)
    if(@evenInd = 1 and @i % 2 = 1 or @evenInd = 0 and @i % 2 = 0) set @prod = @prod * 2
    if(@prod >= 10)  set @prod = @prod/10 + @prod-10 
    set @sum = @sum + @prod
    set @i = @i +1
  END
  if(@sum %10 = 0)
     set @result = 1 
  else
   set @result = 0 
  return @result
end    

Answer 2

This (Bitshift operations in T-SQL) stack overflow article should have what you need to convert the bit shifts from c# into TSQL. You could potentially use the SQL CLR functionality as well to use your .NET code (which may be more performant) instead of converting this to TSQL.