MS Access: Drop Table Function if Exists

I had to check whether a specific table is already available before creating it through Access VBA script. This is very frequent. This code helped me.

Public Function ifTableExists(tablename As String) As Boolean

ifTableExists = False
If DCount("[Name]", "MSysObjects", "[Name] = '" & tablename & "'") = 1 Then
ifTableExists = True
Else
ifTableExists = False
End If

End Function

After creating above UserDefined Function it can be used as

If ifTableExists("MyTable") Then db.Execute "DROP Table MyTable"

SQL Server: Remove special characters from a string

I needed a function to remove special characters from a column. So I created below function.

CREATE FUNCTION [dbo].[fnRemoveSpecialchars]
(
    @String varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
    DECLARE @Clearstring varchar(255)
    Set @Clearstring = LTrim(RTrim(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace
    (Replace(Replace(Replace(@String,'~',''),'`',''),'?','')
    ,'>',''),'<',''),',',''),':',''),';',''),']',''),'[',''),'}',''),'{',''),'|',''),'+','')
    ,'=',''),'_',''),')',''),'(',''),'&',''),'^',''),'%',''),'$',''),'@',''),'!',''),Char(39),'')
    ,'#',''),'*',''),'"',''),'-',''),'.',''),'\',''),'/',''),' ','')))
    RETURN @Clearstring
END

Usage: SELECT 'String_with$special&chars',[dbo].[fnRemoveSpecialchars]('String_with$special&chars')

Another way to remove special characters and parse only alphanumeric characters is

CREATE FUNCTION [dbo].[UDF_ParseAlphaChars]
    (
        @string VARCHAR(8000)
    )
RETURNS VARCHAR(8000)
            AS
    BEGIN
    DECLARE    @IncorrectCharLoc SMALLINT
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
    WHILE @IncorrectCharLoc > 0
        BEGIN
        SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
        END
    SET @string = @string
    RETURN @string
    END

SQL Server: Parse alphanumeric characters

I needed a function to remove all special characters with existing spaces to be removed in any given string. Here is the function to accomplish it.

CREATE FUNCTION [dbo].[UDF_ParseAlphaChars]
    (
        @string VARCHAR(8000)
    )
RETURNS VARCHAR(8000)
            AS
    BEGIN
    DECLARE    @IncorrectCharLoc SMALLINT
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
    WHILE @IncorrectCharLoc > 0
        BEGIN
        SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
        SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
        END
    SET @string = @string
    RETURN @string
    END