Have you ever wondered how to clean fields in SQL? Usually, you have to
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]
DECLARE @OutputFieldRecord VARCHAR(8000)
SET @OutputFieldRecord = LTRIM(RTRIM(@InputFieldRecord))
IF @OutputFieldRecord LIKE '% %' BEGIN
SET @OutputFieldRecord = REPLACE(@OutputFieldRecord, ' ', ' ')
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
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