Here is the requirement:
Find all teachers whose FirstName length is less than 5 and the first 3 characters of their FirstName and LastName are the same
I tried this query (Scalar Function):
CREATE OR ALTER FUNCTION dbo.fn_TeacherFirstName (@TeacherID int)
RETURNS NVARCHAR(20)
AS
BEGIN
DECLARE @Result NVARCHAR(20)
SELECT @Result = LEN(FirstName) < 5 AND LEFT(FirstName,3) = LEFT(LastName,3)
FROM dbo.Teacher
WHERE @TeacherID = ID
RETURN @Result
END
To call function:
--CALL FUNCTION
select *, dbo.fn_TeacherFirstName (ID) AS Result
from dbo.Teacher t
But, when I execute first query, it shows error:
Incorrect syntax near '<'.
Can anyone help me with this?
CodePudding user response:
Just use a normal query, not a function. Also there is no boolean
type in SQL Server, use bit
instead.
SELECT *,
Result = CAST(CASE WHEN LEN(t.FirstName) < 5 AND LEFT(t.FirstName, 3) = LEFT(t.LastName, 3) THEN 1 END AS bit)
FROM dbo.Teacher t