UDF to check if a string is a Valid Roman Number

UDFs are a great place to experiment a lot of SQL Server capabilities. We can do wonders using UDFs. One such requirement came from one of the user in the User group meeting I attended. The user wanted to validate a Roman Number in a string. And was finding any solutions around. The first thing that came to my mind was to use a UDF to work around this problem. Here is how I approached the same...

--- Code block starts ---
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE FUNCTION udf_ISRoman (@Input VARCHAR(100)) 
-- Returns true if the string is a valid Roman numeral.
RETURNS Bit
As
BEGIN
      DECLARE @Counter int, 
            @Result bit
      SET @Result=1
      
      -- Just Loop through and see if each character is valid
      If Len( Replace( Replace( Replace( Replace( Replace( Replace( Replace( 
            @Input, 'I',''), 'V',''), 'X',''), 'L',''), 'C',''), 
            'D',''), 'M','')) <> 0
            SET @Result=0
      
      -- Invalidate precedences which are not legal for ROMAN numbers
      IF (CHARINDEX('IL',UPPER(@Input))>0) OR (CHARINDEX('IC',UPPER(@Input))>0) 
            OR (CHARINDEX('VM',UPPER(@Input))>0) OR (CHARINDEX('XC',UPPER(@Input))>0) 
            OR (CHARINDEX('ID',UPPER(@Input))>0) OR (CHARINDEX('IM',UPPER(@Input))>0) 
            OR (CHARINDEX('VX',UPPER(@Input))>0) OR (CHARINDEX('VL',UPPER(@Input))>0) 
            OR (CHARINDEX('LM',UPPER(@Input))>0) OR (CHARINDEX('CM',UPPER(@Input))>0) 
            OR (CHARINDEX('XD',UPPER(@Input))>0) OR (CHARINDEX('XM',UPPER(@Input))>0) 
            OR (CHARINDEX('VC',UPPER(@Input))>0) OR (CHARINDEX('VD',UPPER(@Input))>0) 
            OR (CHARINDEX('LC',UPPER(@Input))>0) OR (CHARINDEX('LD',UPPER(@Input))>0) 
            OR (CHARINDEX('DM',UPPER(@Input))>0) OR (CHARINDEX('IIII',UPPER(@Input))>0) 
            OR (CHARINDEX('VV',UPPER(@Input))>0) OR (CHARINDEX('XXXX',UPPER(@Input))>0) 
            OR (CHARINDEX('LL',UPPER(@Input))>0) OR (CHARINDEX('CCCC',UPPER(@Input))>0) 
            OR (CHARINDEX('DD',UPPER(@Input))>0) OR (CHARINDEX('MMMMM',UPPER(@Input))>0)
            OR (CHARINDEX('IIV',UPPER(@Input))>0) OR (CHARINDEX('IIX',UPPER(@Input))>0) 
            OR (CHARINDEX('XXL',UPPER(@Input))>0) OR (CHARINDEX('XXC',UPPER(@Input))>0) 
            OR (CHARINDEX('CCD',UPPER(@Input))>0) OR (CHARINDEX('CCM',UPPER(@Input))>0)
            SET @Result=0
 
      Return @Result
END
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--- Code block Ends ---

A typical example with an output would look like this:

Select dbo.udf_ISRoman('CIXVA')

Ouptut of the same would yield a 0 or 1 to denote a success or failure. 0 represents failure in the above code block. The above code yields 0 as "A" is not a valid character and on removing the same you would get 1 indicating a valid numeral. It is quite strange that even Roman numbers are still in use at some places.

As and when more conditions come up we can add the same to the UDF and make it more robust. This is the initial validations we can perform. The function can mature as time passes by.