Monday, September 6, 2010

MS-SQL - User Defined Contain function in MS-SQL

The C# String.Contains method returns true if and only if this string contains the specified sequence of char values.
If the specified value contains in string then it will return true othervise return false.so i have created user defined contain function in MS-SQL like C# String.Contains() method.

Let see how to create user defined contain function in MS-SQL

Step 1
Create a user defined contain function,it is look lie this


CREATE FUNCTION [dbo].[Contain](@Value nvarchar(MAX),@FindWord nvarchar(max))
RETURNS BIT
AS
    BEGIN
        
        DECLARE @CharPostion INT
        DECLARE @Flag BIT
        
            SET @CharPostion=CHARINDEX(@FindWord,@Value)
            
            IF @CharPostion >0
                BEGIN
                    SET @Flag=1  
                END
            ELSE
                BEGIN
                    SET @Flag=0 
                END   
                
        RETURN (@Flag)
    
    END

Step 2
Call a user defined contain function,it is look like this


DECLARE @String NVARCHAR(MAX)
SET @String='I like to write query in MS-SQL'

SELECT dbo.Contain(@String,'query') as Status  -- return 1

SELECT dbo.Contain(@String,'script') as Status -- return 0  

Download
Download Script

No comments:

Post a Comment