Have you ever wondered how to clean fields in SQL, usually you have to LTRIM and RTRIM on each Field Object on your select statement which I used to do before when I was starting with SQL years ago, but in case you dont know there is what you call custom function. Heres how it works
First you have to create your user defined function and on my sample I will use my custom cleaning function that trims the spaces, remove double spaces and adhere to the camel casing. To do that run this code in your Query Builder
CREATE FUNCTION [dbo].[ufn_CleanField] ( @InputFieldRecord VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @OutputFieldRecord VARCHAR(8000) -- Trim Data SET @OutputFieldRecord = LTRIM(RTRIM(@InputFieldRecord)) -- Double Spaces to single spaces IF @OutputFieldRecord LIKE '% %' -- double spaces BEGIN SET @OutputFieldRecord = REPLACE(@OutputFieldRecord, ' ', ' ') END -- To Title Case DECLARE @Reset bit; DECLARE @ProcessFieldRecord varchar(8000); DECLARE @i int; DECLARE @c char(1); SELECT @Reset = 1, @i=1, @ProcessFieldRecord = ''; WHILE (@i <= LEN(@OutputFieldRecord)) SELECT @c= SUBSTRING(@OutputFieldRecord, @i, 1), @ProcessFieldRecord = @ProcessFieldRecord + CASE WHEN @Reset=1 THEN UPPER(@c) ELSE LOWER(@c) END, @Reset = CASE WHEN @c like '[a-zA-Z]' THEN 0 ELSE 1 END, @i = @i +1 SET @OutputFieldRecord = @ProcessFieldRecord RETURN @OutputFieldRecord END
After running it you will see a new custom function in the Scalar-valued Functions under the Functions Section of your SQL Server. Now why does it show on the Scalar-valued functions and not on the others?
To give a rough idea on what shows where, here is a definition of each:
Aggregate Functions – A Function that return a single value, calculated from values in a column.
Scalar-valued Functions – A Function that return a single value, based on the input value.
Table-Valued Functions – A Function that return a table data type
System Functions – As the name implies functions that are provided by the system, these are the built in SQL Functions.
Now you have the Function now you can use it in your Queries like such:
SELECT dbo.ufn_CleanField(FirstName) FROM Employees