Function to Clean Fields in SQL

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