Download PowerPivot V1

by Amol 26. August 2013 20:32

Tags:

Downloads

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

Category

Recent Posts

Tag cloud