Proper Case T-SQL

by Amol 30. January 2013 04:57
CREATE FUNCTION dbo.udfProperCase
(
    @Input as varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN   
    DECLARE @Reset bit,
            @Proper varchar(8000),
            @Counter int,
            @FirstChar char(1)
               
    SELECT @Reset = 1, @Counter = 1, @Proper = ''
    
    WHILE (@Counter <= LEN(@Input))
    BEGIN
        SELECT  @FirstChar = SUBSTRING(@Input, @Counter, 1),
                @Proper = @Proper + CASE WHEN @Reset = 1 THEN UPPER(@FirstChar) ELSE LOWER(@FirstChar) END,
                @Reset = CASE WHEN @FirstChar LIKE '[a-zA-Z]' THEN 0 ELSE 1 END,
                @Counter = @Counter + 1
    END
    
    SELECT @Proper = REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Proper)),'  ',' '+ CHAR(7)) , CHAR(7)+' ',''), CHAR(7),'')
    WHERE CHARINDEX('  ', @Proper) > 0 
    
    RETURN @Proper
    
END
GO

SELECT dbo.udfProperCase ('amol     RAJMANE')
GO

Tags:

SQL Server Scripts

Load Delimited CSV file using ACE Driver

by Amol 14. December 2012 10:20

ACE driver will read the comma-delimited file without any issues. But if you have different delimiter it doesn’t work as expected.

Earlier we used to use FORMAT=Delimited(|) as a switch in connection string to load different delimiter CSV file.

 

To read the file with different delimiter, you need to create Schema.ini file in the same directory as the SCV file. And add the below contents in it.

 

[SampleFile.csv]

Format=Delimited(|)

ColNameHeader=False

 

Once you ready with the Schema.ini file run the below command to read the CSV file using ACE driver

 

SELECT TOP *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\Temp\;HDR=No',

'SELECT * FROM [SampleFile.csv]')

 

Tags: , ,

General | SQL Server Scripts

Remove Non Alphanumeric Characters

by Amol 20. July 2012 08:45

SQL Query to remove non alphanumeric characters

CREATE FUNCTION dbo.RemoveNonAlphaNumericCharacters

(

      @Str nvarchar(255)

)

RETURNS int

AS

BEGIN

 

      DECLARE @Result varchar(255)

      SET @Result = 1

 

      DECLARE @nchar nvarchar(1)

      DECLARE @position int

 

      SET @position = 1

      WHILE @position <= LEN(@Str)

      BEGIN

 

            SET @nchar = SUBSTRING(@Str, @position, 1)

            IF (ASCII(@nchar) between 32 and 128)

            BEGIN

                SET @Result = 1

            END

            ELSE

            BEGIN

                SET @Result = 0

                BREAK

            END

            SET @position = @position + 1

      END

 

      RETURN @Result

END 

Tags: ,

SQL Server Scripts

Category

Recent Posts

Tag cloud