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 don't know, there is what you call a custom function. Here's how it works.

First, you have to create your user defined function and in my sample, I will use my custom cleaning function that trims the spaces, removes double spaces and adheres 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 of what shows where, here is a definition of each:

• Aggregate functions – A function that returns a single value, calculated from values in a column.
• Scalar-valued functions – A function that returns a single value, based on the input value.
• Table-Valued functions – A function that returns 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. You can use it in your queries like such:

SELECT dbo.ufn_CleanField(FirstName) FROM Employees