SQL server ALLTRIM()

To trim leading spaces you can use LTRIM() and to remove trailing spaces you can use RTRIM() and to trim all you can use LTRIM(RTRIM(xxx)) but these functions do not account for non-printing characters that you may encounter when importing from a csv, spreadsheet etc.

e.g. tab is char(9) and then there’s carriage return and line feed and other non-printing chars. We want to remove all of this, not just the spaces.

Method. Use this AllTrim function that I wrote.

Look for the first occurrence of a character you want , trim up to here and reverse the remainder and then do this again. Finally re-reverse the string.

Note that this requires a number table which is best stored so you don’t have to keep recreating it.

This is my code so please use it in entirety including my header comment


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: David Bridge
-- David Bridge Technology Limited
-- http://www.davidbridgetechnology.com
-- Create date: April 2011
-- Description: Returns a fully trimmed varchar
-- ignore whitespace, tabs, carriage returns etc until you find something.
-- Save the string
-- reverse it
-- do it again
-- return the string
-- =============================================
ALTER FUNCTION dbo.ALLTRIM
(
@str varchar(max)
)
RETURNS varchar(max)
AS
BEGIN

DECLARE @ReturnString varchar(max) = '' -- default in case of nothing useful in string
DECLARE @tmpString varchar(max) = @str
DECLARE @StringLength int = DATALENGTH(@tmpString)

--Create 0 based number table (best making a permanent one of these and indexing it properly for reuse)
DECLARE @number TABLE (number int PRIMARY KEY)
DECLARE @increment int = 0
while (@increment < 1000)
BEGIN
INSERT INTO @number (number) VALUES (@increment)
SET @increment = @increment + 1
END

-- Main part of function
-- Find position of the first useful char
DECLARE @StartPosition int
SELECT top 1
@StartPosition = Number
FROM @number
WHERE
Number <= DATALENGTH(@tmpString) - 1
AND SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[0-9]'
OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[a-z]'
OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[A-Z]'

if @StartPosition IS NOT NULL
BEGIN
-- found a useful char so trim trailing chars to here
SET @tmpString = SUBSTRING(@tmpString,@StartPosition+1,@StringLength)

-- Now just reverse the string and do the same again
SET @tmpString = REVERSE(@tmpString)

SELECT top 1
@StartPosition = Number
FROM @number
WHERE
Number <= DATALENGTH(@tmpString) - 1
AND SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[0-9]'
OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[a-z]'
OR SUBSTRING(@tmpString, Number + 1, 1 ) LIKE '[A-Z]'

if @StartPosition IS NOT NULL
SET @tmpString = SUBSTRING(@tmpString,@StartPosition+1,@StringLength)

-- and finally re-reverse the string
SET @ReturnString = REVERSE(@tmpString)
END

RETURN @ReturnString

END

Thanks

Dave

About davidbridge

I run David Bridge Technology Limited and specialise in Database design and development including administration and web application development using .net
This entry was posted in Developer stuff, SQL Stuff and tagged , , , . Bookmark the permalink.

3 Responses to SQL server ALLTRIM()

  1. Alan Russell says:

    Thank you very much, Dave, for posting this superb piece of code. It totally fixed a problem which I had in our SQL Server 2005 database. I had to slightly modify the code to run on 2005 (moving the assignments from the “DECLARE” statements to separate “SET” statements), but I did of course leave all your header comment. If you would like me to send you the modified code which runs fine on our database, I’d be happy to do so. Again, thanks very much.

  2. SteveD says:

    Dave,

    This is exactly what I was in need of. I miss the alltrim() function from by dBase/Clipper days. Yes dating myself a bit.. Wonderful code. I have tested it, and plan on installing it as a permanent function in my system. Header intact..

    My Very Best,
    Steve

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s