Data Cleansing Scripts in SQL Server

1 - Remove space from string.
2 - Remove New Line Character from an string.
3 - Remove numerics from an string.
4 - Count the occurance of character sent in second parameter.
5 - Converts an string in proper case.


1 - Remove space from string.
-- =============================================
-- Author: <Shagaf Khot>
-- Description: Remove space from string
-- =============================================
CREATE FUNCTION Remove_spaces(@str varchar(8000)) RETURNS varchar(8000)
AS
BEGIN
   WHILE CHARINDEX(SPACE(2), @str) > 0
      SET @str = REPLACE(@str, SPACE(2),SPACE(1))      RETURN Ltrim(Rtrim(@str))
END
Go
2 - Remove New Line Character from an string.
-- =============================================
-- Author: <Shagaf Khot>
-- Description: Remove New Line Char from string
-- =============================================
CREATE FUNCTION Remove_NewLineChar(@str varchar(8000)) RETURNS varchar(8000)
AS
BEGIN
   Declare @CR NVarchar(10)
   Set @CR = Char(13)+Char(10)
   WHILE CHARINDEX(@CR, @str) > 0
      SET @str = REPLACE(@str, @CR,SPACE(1))         RETURN Ltrim(Rtrim(@str))   END
Go
3 - Remove numerics from an string.
-- =============================================
-- Author: <Shagaf Khot>
-- Description: Function to remove numerics from string
-- =============================================
Create FUNCTION ExtractString(@String NVARCHAR(2000)) RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers NVARCHAR(1000)
SET @Count = 0
SET @IntNumbers = ''
   WHILE @Count <= LEN(@String)   
BEGIN
      IF SUBSTRING(@String,@Count,1)>= '0' AND SUBSTRING(@String,@Count,1) <= '9'      BEGIN         SELECT @IntNumbers = CASE WHEN @IntNumbers =''  
         THEN REPLACE(@String,SUBSTRING@String,@Count,1),SPACE(0))       ELSE 
         REPLACE(@IntNumbers,SUBSTRING(@String,@Count,1),SPACE(0))
      End  
 END   SET @Count = @Count + 1  
END
      RETURN @IntNumbers 
END
GO

4 - Count the occurance of character sent in second parameter.
-- =============================================
-- Author: <Shagaf Khot>
-- Description: Count the occurance of character sent in second parameter
-- =============================================
CREATE FUNCTION [dbo].[ufn_CountChar]
( @pInput VARCHAR(1000), @pSearchChar CHAR(1)) RETURNS INT
BEGIN
   DECLARE @vInputLength INT   DECLARE @vIndex INT
   DECLARE @vCount INT
   SET @vCount = 0
   SET @vIndex = 1
   SET @vInputLength =
   LEN(@pInput)

   WHILE @vIndex <= @vInputLength
   BEGIN
   IF SUBSTRING(@pInput, @vIndex, 1)= @pSearchChar      SET @vCount = @vCount + 1      SET @vIndex = @vIndex + 1   END   RETURN @vCount
END
GO

5 - Converts an string in proper case.
-- =============================================
-- Author: <Shagaf Khot>
-- Description: Converts an string in proper case
-- =============================================
Create FUNCTION ProperCase(@string varchar(8000)) returns varchar(8000)
As
BEGIN
SET @string = lower(@string)DECLARE @i intSET @i = ASCII('a')WHILE @i <= ASCII('z')    Begin        Print (char(@i))        SET @string = REPLACE( @string,' '+CHAR(@i),' '+CHAR(@i-32))
        SET @i = @i + 1
    END
    SET @string = char(ASCII(left(@string, 1))-32)+right(@string,LEN(@string)-1)
    RETURN @string
END

GO
5 - Converts an string in proper case.
-- =============================================
-- Author: <Shagaf Khot>
-- Description: Script to find a single quote
-- =============================================
CREATE TABLE [dbo].[Abc]
(
[no1] [int]
[Name1] [nvarchar](50) NULL
)
GO
Declare @a varchar(100)
Set @a = 'abc'+CHAR(39)+' test'+CHAR(39)+' test'+CHAR(39)+' test'
Insert Into Abc(NO1,Name1) Values(5,@a)
SELECT * from Abc Where Name1 like '%''%'NULL,