I am new to SQL Server. I created a function which action like charindex

I am new to SQL server. I am trying to create a scalar-valued function which will act like charindex.

IF EXISTS(SELET * FROM sysobjects 
          WHERE id = OBJECT_ID(N'My_search_string')
            AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION My_Search_String
GO

CREATE FUNCTION My_Search_String
    (@target_string varchar,
     @string varchar)
RETURNS INT 
AS
BEGIN
    DECLARE @result int;
    SET @result = CHARINDEX(@target_string, @string)
    RETURN @result 
END
GO

but I am not so sure why when I call the function like

select dbo.My_Search_string('cd','abcde')
go

it return 0 as result. Thank you so much for your help 🙂 have a great day guys

Answer

In SQL Server, you need to specify lengths on string datatypes. If you don’t, it assumes length 1.

Therefore your create function line should look like

Create Function My_Search_String(@target_string varchar(50),@string varchar(50))