I need to clean up the data that users input into the database. How can I remove extraneous spaces in the character (char) column in SQL Server 6.5?
A Although the question asks for a SQL Server 6.5 solution, let's first look at the SQL Server 2000 solution for removing extraneous spaces. In SQL Server 2000, you can implement a user-defined function (UDF), such as the one that Listing 1 shows, to remove the spaces. The UDF loops through the string argument, looking for repeating spaces; when it finds repeating spaces, the code includes only the first space in the returned string. Note that in Listing 1's mytrim() function, if you pass a NULL value, SQL Server returns a NULL value. If the string is empty or contains only spaces, SQL Server returns an empty string. And the code removes only extraneous spaces.
To use the UDF that Listing 1 shows with pre-SQL Server 2000 releases, which don't support UDFs, you must make some modifications. You can either place the code inline or create a stored procedure and use an output parameter to return the result. Then, you can use the result in your final SELECT statement. However, if you use a stored procedure, you have to pass data through the procedure by using a cursor loop because the stored procedure can't accept a tabular argument. The advantage of using a UDF instead of inline code or a stored procedure is that T-SQL syntax lets you apply the UDF to all char column occurrences in your SELECT statement without the need for a loop.