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.


  Modified On Jul-04-2019 06:44:05 AM

Leave Comment