---
title: "how to create scalar function in sql"  
description: "how to create scalar function in sql"  
author: "Anonymous User"  
published: 2018-07-09  
updated: 2018-07-10  
canonical: https://www.mindstick.com/forum/34598/how-to-create-scalar-function-in-sql  
category: "mssql server"  
tags: ["sql server", "sql"]  
reading_time: 2 minutes  

---

# how to create scalar function in sql

i have need to create scalar [function in sql](https://www.mindstick.com/forum/156839/what-is-function-in-sql-server).

please help me.

## Replies

### Reply by Prakash nidhi Verma

A CREATE FUNCTION(scalar):

- Specify a name for the function.

- data type for each input parameter.

- RETURNS keyword and the data type of the scalar return value.

Example :

```
CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)              RETURNS  DECIMAL(10,3)
    LANGUAGE SQL
    MODIFIES SQL
    BEGIN
      DECLARE price DECIMAL(10,3);
      IF Vendor = 'Vendor 1'
        THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid);
      ELSE IF Vendor = 'Vendor 2'
        THEN SET price = (SELECT Price
                          FROM V2Table
             WHERE Pid = GetPrice.Pid);
      END IF;
  RETURN price;
  END
```

```
SELECT dbo.function (parameters)
```

### Reply by Anonymous User

Here we create a user-defined (**Scalar-valued [function](https://www.mindstick.com/articles/13001/multi-statement-table-valued-user-defined-function-in-sql-server)**) which remove html tag from string. It is a simple function that need a string parameter;

Example Of [SQL Functions](https://www.mindstick.com/Articles/950/some-sql-functions)

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

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


---

Original Source: https://www.mindstick.com/forum/34598/how-to-create-scalar-function-in-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.