I am new to SQL server. I am trying to create a scalar-valued function which will act like charindex. but I am not so sure why when I call the function like 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

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

​x
 
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_StringGO​CREATE FUNCTION My_Search_String    (@target_string varchar,     @string varchar)RETURNS INT ASBEGIN    DECLARE @result int;    SET @result = CHARINDEX(@target_string, @string)    RETURN @result ENDGO​

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

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

 
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))

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

Advertisement

Answer