T-SQL to get string between two strings

by Amol 17. July 2013 09:17

Below is the user defined function created in SQL Server to find the string between two strings.

 

CREATE FUNCTION dbo.udf_GetStringBetween2Strings 
(
	@String varchar(max), 
	@FirstString varchar(256), 
	@SecondString varchar(256)
)
RETURNS VARCHAR(max) 
AS
BEGIN

	DECLARE @FirstPosition int,  @SecondPosition int
	SET @FirstPosition = CHARINDEX(@FirstString,@String) + LEN(@FirstString)
	SET @SecondPosition = CHARINDEX(@SecondString,@String) 
	RETURN (SELECT SUBSTRING(@String, @FirstPosition, @SecondPosition - @FirstPosition))

END
GO

DECLARE @MyString varchar(256) = 'Bla bla bla xyxz Find my name Amol Rajmane in this string.'
SELECT dbo.udf_GetStringBetween2Strings (@MyString, 'Find my name ', ' in this') 
GO

 

 

Result is "Amol Rajmane"

Tags: ,

General

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

Category

Recent Posts

Tag cloud