CREATE    FUNCTION dbo.aif_removecolons

/*    Remove colon characters from string

    2005-08-04    George Curran, Computer Associates
            Add grant execute to "aiadmin" role
    2004-05-05    George Curran, Computer Associates
*/

(
--    Source text string
    @strSource nvarchar(2048)
)
RETURNS nvarchar(2048)
AS
BEGIN

    DECLARE @intPos int
    DECLARE @strBeg nvarchar(2048)
    DECLARE @strEnd nvarchar(2048)

--    Exit and return empty string if a text string is empty or NULL
    IF      (@strSource = '')
    OR    (@strSource IS NULL)
        RETURN     ''

--    Trim special characters from the left
    SET    @strSource = LTRIM(@strSource)

--    Delete last character if it is not 0-9 or a-Z
    WHILE    @strSource LIKE '%[:]%'
    BEGIN
        SET    @intPos = CHARINDEX(':', @strSource)
        SET    @strBeg = LEFT(@strSource, @intPos - 1)
        SET    @strEnd = SUBSTRING(@strSource, @intPos + 1, 2048)
        IF    (RIGHT(@strBeg, 1) = ' ')
        AND    (LEFT(@strEnd, 1) = ' ')
            SET @strBeg = RTRIM(@strBeg)
        SET    @strSource = @strBeg + @strEnd
    END

--    Return empty string if length of the resulting string is not equal
--    to 12
    IF    (LEN(@strSource) != 12)
        SET @strSource = ''

--    Return the command
    RETURN    @strSource

END

GO
GRANT EXECUTE ON  [dbo].[aif_removecolons] TO [aiadmin]
GO