Remove bad characters from a string

This simple CTE can be used to remove bad characters in string. It will take string and allowed characters and returns neat string. Change the @AllowedPattern as you wish and check the output string. For example if you have a string abc@#$()123 and you want a string abc123, Set allowedPattern to '0-9a-z. With little tweak this can be converted to a function.





DECLARE @String VARCHAR(8000),
@AllowedPattern VARCHAR(100),
@Holder VARCHAR(8000)
SET @String = '!@#$% 123 ??abc##rd,,,'
SET @AllowedPattern = '0-9a-z'
SET @Holder = ''

;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS num
          FROM t4 x, t4 y)
SELECT @Holder = @Holder + SUBSTRING(@String,num,1)
FROM tally
WHERE num <= LEN(@String)
AND PATINDEX('['+@AllowedPattern+']',SUBSTRING(@String,num,1)) = 1

SELECT @Holder

Convert a given string into Lowercase without using LOWER..

Continuation to previous post...converting a string to lower case string

CREATE FUNCTION udfLower

(

   @InputString NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

   DECLARE @LowerString    NVARCHAR(MAX)  = ''

   DECLARE @Counter        INT            = 1

   DECLARE @Character      CHAR           = ''

   DECLARE @AsciiValue     TINYINT

   DECLARE @NewAsciiValue  TINYINT

   WHILE (@Counter <= LEN(@InputString))

   BEGIN

      SET @Character = SUBSTRING(@InputString,@Counter,1)

      IF ASCII(@Character) BETWEEN 65 AND 90

      BEGIN

         SET @NewAsciiValue = ASCII(@Character) + 32

         SET @LowerString = @LowerString + CHAR(@newAsciiValue)

      END

      ELSE

      BEGIN

         SET @LowerString = @LowerString + @Character

      END

      SET @Counter = @Counter + 1

   END

   RETURN @LowerString

END

GO

SELECT dbo.udfLower('this is a test STRingdadas@#@#@')

Convert given string into upper case with out using builtin UPPER function

How to convert a given string into upper case string without using builtin UPPER function. Below is the code.

CREATE FUNCTION udfUpper

(

   @InputString NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

   DECLARE @UpperString    NVARCHAR(MAX)  = ''

   DECLARE @Counter        INT            = 1

   DECLARE @Character      CHAR           = ''

   DECLARE @AsciiValue     TINYINT

   DECLARE @NewAsciiValue  TINYINT

   WHILE (@Counter <= LEN(@InputString))

   BEGIN

      SET @Character = SUBSTRING(@InputString,@Counter,1)

      IF ASCII(@Character) BETWEEN 97 AND 122

      BEGIN

         SET @NewAsciiValue = ASCII(@Character) - 32

         SET @UpperString = @UpperString + CHAR(@newAsciiValue)

      END

      ELSE

      BEGIN

         SET @UpperString = @UpperString + @Character

      END

      SET @Counter = @Counter + 1

   END

   RETURN @UpperString

END

GO

SELECT dbo.udfUpper('this is a test STRingdadas@#@#@')