Click here to Skip to main content
15,867,756 members
Articles / Programming Languages / SQL

Function to Clean Fields in SQL

Rate me:
Please Sign up or sign in to vote.
4.67/5 (3 votes)
23 Mar 2010CPOL1 min read 20.1K   6   1
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:

SQL
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.

SQL Functions

Now you have the function. You can use it in your queries like such:

SQL
SELECT dbo.ufn_CleanField(FirstName) FROM Employees

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

Comments and Discussions

 
GeneralUnicode with bigger text version Pin
Zanoon8-Jul-10 1:18
Zanoon8-Jul-10 1:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.