Introduction:

Here we are going to explain what is scalar function in sql or how to create scalar function in sql or how to use scalar function in sql server with example.

Description:

SQL Server scalar function is a function that operates on scalar values -- that is, it takes one (or more) input values as parameters directly and returns a value.

Example of scalar function:

CREATE FUNCTION [dbo].[RemoveHtmlFronString] (@HTMLText VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @Start INT

DECLARE @End INT

DECLARE @Length INT

SET @Start = CHARINDEX('<',@HTMLText) SET @End = 

CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText)) 

SET @Length = (@End - @Start) + 1 WHILE @Start > 0

AND @End > 0

AND @Length > 0

BEGIN

SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')

SET @Start = CHARINDEX('<',@HTMLText) SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))

SET @Length = (@End - @Start) + 1

END

RETURN LTRIM(RTRIM(@HTMLText))

END

Use of scalar function:

PRINT [dbo].[RemoveHtmlFronString]('<b> Hello</b>')

In above example I have created a scalar function that take a string type parameter.

It will remove all HTML tags from string and return plain text or string.

I hope it will help to after reading it.