Как проверить номер ICCID SIM-карты в TSQL?

В этой (операции битового сдвига в T-SQL) статье о переполнении стека должно быть то, что вам нужно конвертировать битовые сдвиги из С# в TSQL. Вы также можете использовать функциональность SQL CLR, чтобы использовать свой код .NET (который может быть более производительным) вместо преобразования его в TSQL.

Я нашел это!

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

Я только что переделал интернет-версию, чтобы она работала быстрее и без временной таблицы:

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