SQL Exercise:
Create a SQL function that takes a string as input and removes all special characters (keeping only alphanumeric characters and spaces).
The following is a list of special characters that need to be removed from the string:
@!#$%*()^-_=+{}[]|\/:;”””&<>./,?
Solution:
CREATE FUNCTION dbo.RemoveSpecialCharacters (@InputString NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
-- List of characters to remove
DECLARE @SpecialChars NVARCHAR(100) = '@!#$%*()^-_=+{}[]|\/:;""''&<>./,?';
-- Replacement characters (empty spaces matching length of @SpecialChars)
DECLARE @ReplacementChars NVARCHAR(100) = REPLICATE(' ', LEN(@SpecialChars));
-- Use TRANSLATE to replace special characters with spaces, then remove spaces using REPLACE
RETURN REPLACE(TRANSLATE(@InputString, @SpecialChars, @ReplacementChars), ' ', '');
END;
Let’s test the function.
DECLARE @InputString NVARCHAR(MAX) = 'ABCDE%12&#$()45BCF!@'; SELECT dbo.RemoveSpecialCharacters(@InputString) AS OutputString;
Output:
Explanation:
- @SpecialChars:
- Contains the list of special characters you want to remove.
- @ReplacementChars:
- Replaces each special character with a space. The REPLICATE function creates a string of spaces equal to the length of @SpecialChars.
- TRANSLATE:
- TRANSLATE Maps each character in @SpecialChars to the corresponding character in @ReplacementChars (spaces).
- REPLACE:
- Removes the spaces introduced by the TRANSLATE function.
