Simple question re autotrim varchar

I have a table with a number of varchar fields - name, address, etc. 
Many values were stored with trailing spaces, and a few with leading spaces.

It's easy enough to clean up the existing data, but is there a way to 
automatically trim these when they get entered in the first place? I 
could write an update trigger, but that seems like overkill for this 
task. Is there a simpler way to do it?

If not, I could do in in the front end, but I'd rather handle it in the 
table.


0
Paul
9/9/2010 5:48:42 PM
sqlserver.programming 1873 articles. 0 followers. Follow

10 Replies
2255 Views

Similar Articles

[PageSpeed] 24

1) Add "CHECK (foobar = RTRIM(LTRIM(foobar)))" to the columns
2) Add computed columns to the table
3) allow insertion only via a stored procedure


0
CELKO
9/9/2010 7:10:15 PM
On Thu, 09 Sep 2010 10:48:42 -0700, Paul Pedersen <asdf@asdf.com>
wrote:

>I have a table with a number of varchar fields - name, address, etc. 
>Many values were stored with trailing spaces, and a few with leading spaces.
>
>It's easy enough to clean up the existing data, but is there a way to 
>automatically trim these when they get entered in the first place? I 
>could write an update trigger, but that seems like overkill for this 
>task. Is there a simpler way to do it?
>
>If not, I could do in in the front end, but I'd rather handle it in the 
>table.
>

Hi

With the ANSI_PADDING setting to OFF trailing blanks in character
values inserted into a varchar column are trimmed. See
http://msdn.microsoft.com/en-us/library/ms187403.aspx


John
0
John
9/9/2010 7:50:08 PM
Paul Pedersen (asdf@asdf.com) writes:
> I have a table with a number of varchar fields - name, address, etc. 
> Many values were stored with trailing spaces, and a few with leading
> spaces. 
> 
> It's easy enough to clean up the existing data, but is there a way to 
> automatically trim these when they get entered in the first place? I 
> could write an update trigger, but that seems like overkill for this 
> task. Is there a simpler way to do it?
> 
> If not, I could do in in the front end, but I'd rather handle it in the 
> table.

CREATE TRIGGER trimmer_tri ON tbl AFTER INSERT AS
   UPDATE tbl
   SET    col = rtrim(ltrim(col))
   FROM   tbl a
   WHERE  EXISTS (SELECT *
                  FROM   inserted 
                  WHERE  a.keycol = i.keycol
                    AND  len(a.col) <> datalength(a.col))

An INSTEAD OF trigger would be more efficient, but you need to duplicate
the INSERT statement which is less appetizing.

Note: if you use nvarchar, the last condition should be:

                    AND  2*len(a.col) <> datalength(a.col))



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/9/2010 10:09:17 PM
On 9/9/2010 12:50 PM, John Bell wrote:
> On Thu, 09 Sep 2010 10:48:42 -0700, Paul Pedersen<asdf@asdf.com>
> wrote:
>
>> I have a table with a number of varchar fields - name, address, etc.
>> Many values were stored with trailing spaces, and a few with leading spaces.
>>
>> It's easy enough to clean up the existing data, but is there a way to
>> automatically trim these when they get entered in the first place? I
>> could write an update trigger, but that seems like overkill for this
>> task. Is there a simpler way to do it?
>>
>> If not, I could do in in the front end, but I'd rather handle it in the
>> table.
>>
>
> Hi
>
> With the ANSI_PADDING setting to OFF trailing blanks in character
> values inserted into a varchar column are trimmed. See
> http://msdn.microsoft.com/en-us/library/ms187403.aspx
>
>
> John

FYI, from that article:

In a future version of Microsoft SQL Server ANSI_PADDING will always be 
ON and any applications that explicitly set the option to OFF will 
produce an error. Avoid using this feature in new development work, and 
plan to modify applications that currently use this feature.


0
Paul
9/10/2010 9:36:54 PM
On 9/9/2010 12:10 PM, --CELKO-- wrote:
>
> 1) Add "CHECK (foobar = RTRIM(LTRIM(foobar)))" to the columns

Am I mistaken, or will that simply refuse to add any record that has a 
padded foobar value?

I was looking for something simpler than a trigger that would do the 
trimming work for me.


> 2) Add computed columns to the table

To return only trimmed values? That would work, but I'd rather trim them 
before storing them. It just seems cleaner. Besides, I don't want to 
repeat a trim operation every time the field is accessed.


> 3) allow insertion only via a stored procedure

That would do it, but perhaps no simpler than a trigger. It could have 
other benefits though.


Thanks for the help.
0
Paul
9/10/2010 9:42:42 PM
On 9/9/2010 3:09 PM, Erland Sommarskog wrote:
> Paul Pedersen (asdf@asdf.com) writes:
>> I have a table with a number of varchar fields - name, address, etc.
>> Many values were stored with trailing spaces, and a few with leading
>> spaces.
>>
>> It's easy enough to clean up the existing data, but is there a way to
>> automatically trim these when they get entered in the first place? I
>> could write an update trigger, but that seems like overkill for this
>> task. Is there a simpler way to do it?
>>
>> If not, I could do in in the front end, but I'd rather handle it in the
>> table.
>
> CREATE TRIGGER trimmer_tri ON tbl AFTER INSERT AS
>     UPDATE tbl
>     SET    col = rtrim(ltrim(col))
>     FROM   tbl a
>     WHERE  EXISTS (SELECT *
>                    FROM   inserted
>                    WHERE  a.keycol = i.keycol
>                      AND  len(a.col)<>  datalength(a.col))
>
> An INSTEAD OF trigger would be more efficient, but you need to duplicate
> the INSERT statement which is less appetizing.
>
> Note: if you use nvarchar, the last condition should be:
>
>                      AND  2*len(a.col)<>  datalength(a.col))
>
>

Yeah, I thought I might have to use a trigger. Thank you.

0
Paul
9/10/2010 9:44:00 PM
On Fri, 10 Sep 2010 00:09:17 +0200, Erland Sommarskog wrote:

>                    AND  len(a.col) <> datalength(a.col))
>
>An INSTEAD OF trigger would be more efficient, but you need to duplicate
>the INSERT statement which is less appetizing.
>
>Note: if you use nvarchar, the last condition should be:
>
>                    AND  2*len(a.col) <> datalength(a.col))

Or you could avoid this dependancy on the data type and use

    AND (a.col LIKE ' %' OR a.col LIKE '% ')

(Note that there is a space before the first, and after the last percent
sign)

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
0
Hugo
9/10/2010 11:08:13 PM
Hugo Kornelis (hugo@perFact.REMOVETHIS.info.INVALID) writes:
> On Fri, 10 Sep 2010 00:09:17 +0200, Erland Sommarskog wrote:
>>                    AND  len(a.col) <> datalength(a.col))
>>
>>An INSTEAD OF trigger would be more efficient, but you need to duplicate
>>the INSERT statement which is less appetizing.
>>
>>Note: if you use nvarchar, the last condition should be:
>>
>>                    AND  2*len(a.col) <> datalength(a.col))
> 
> Or you could avoid this dependancy on the data type and use
> 
>     AND (a.col LIKE ' %' OR a.col LIKE '% ')
> 
> (Note that there is a space before the first, and after the last percent
> sign)
 
My idea was to avoid any dependency on exactly the space characters, as
there could just as well be tab characters, no-break space (char(160))
and other white-space characters.

However, my testing indicates that rtrim and ltrim does not trim these 
characters. And len() includes them in the length, so that was pretty 
useless. And, in my misguided zeal I introduced another error: the
expression I used, will only cover trailing spaces, not leading spaces.

Not that I was only one to go wrong. Celko suggested this for a 
constraint:

  foobar = RTRIM(LTRIM(foobar))

But this will perimt trailing spaces to be stored.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/11/2010 10:59:55 AM
On Fri, 10 Sep 2010 14:36:54 -0700, Paul Pedersen <asdf@asdf.com>
wrote:

>On 9/9/2010 12:50 PM, John Bell wrote:
>> On Thu, 09 Sep 2010 10:48:42 -0700, Paul Pedersen<asdf@asdf.com>
>> wrote:
>>
>>> I have a table with a number of varchar fields - name, address, etc.
>>> Many values were stored with trailing spaces, and a few with leading spaces.
>>>
>>> It's easy enough to clean up the existing data, but is there a way to
>>> automatically trim these when they get entered in the first place? I
>>> could write an update trigger, but that seems like overkill for this
>>> task. Is there a simpler way to do it?
>>>
>>> If not, I could do in in the front end, but I'd rather handle it in the
>>> table.
>>>
>>
>> Hi
>>
>> With the ANSI_PADDING setting to OFF trailing blanks in character
>> values inserted into a varchar column are trimmed. See
>> http://msdn.microsoft.com/en-us/library/ms187403.aspx
>>
>>
>> John
>
>FYI, from that article:
>
>In a future version of Microsoft SQL Server ANSI_PADDING will always be 
>ON and any applications that explicitly set the option to OFF will 
>produce an error. Avoid using this feature in new development work, and 
>plan to modify applications that currently use this feature.
>

I would stick to doing this at the front end a trigger would execute
on all inserts/updates unless they are disabled which may not be an
option.

John
0
John
9/11/2010 1:16:45 PM
On 9/11/2010 6:16 AM, John Bell wrote:
> On Fri, 10 Sep 2010 14:36:54 -0700, Paul Pedersen<asdf@asdf.com>
> wrote:
>
>> On 9/9/2010 12:50 PM, John Bell wrote:
>>> On Thu, 09 Sep 2010 10:48:42 -0700, Paul Pedersen<asdf@asdf.com>
>>> wrote:
>>>
>>>> I have a table with a number of varchar fields - name, address, etc.
>>>> Many values were stored with trailing spaces, and a few with leading spaces.
>>>>
>>>> It's easy enough to clean up the existing data, but is there a way to
>>>> automatically trim these when they get entered in the first place? I
>>>> could write an update trigger, but that seems like overkill for this
>>>> task. Is there a simpler way to do it?
>>>>
>>>> If not, I could do in in the front end, but I'd rather handle it in the
>>>> table.
>>>>
>>>
>>> Hi
>>>
>>> With the ANSI_PADDING setting to OFF trailing blanks in character
>>> values inserted into a varchar column are trimmed. See
>>> http://msdn.microsoft.com/en-us/library/ms187403.aspx
>>>
>>>
>>> John
>>
>> FYI, from that article:
>>
>> In a future version of Microsoft SQL Server ANSI_PADDING will always be
>> ON and any applications that explicitly set the option to OFF will
>> produce an error. Avoid using this feature in new development work, and
>> plan to modify applications that currently use this feature.
>>
>
> I would stick to doing this at the front end a trigger would execute
> on all inserts/updates unless they are disabled which may not be an
> option.
>
> John

Yes, I think so too. Doing it on the front end is probably the best way 
to go.

Names, addresses, and many other data items are never padded with spaces 
on the left or right, so a column attribute that would automatically 
trim on save would be a very useful feature. We'll probably never see it 
though - it makes too much sense.

0
Paul
9/11/2010 9:19:27 PM
Reply:

Similar Artilces:

A few MVVM and Entity Framework questions
I've been out of the .NET game for a few years now [pre 2005] and starting a little project using the Entity Framework and WPF. My understanding is that the MVVM pattern is the way to go nowadays, but I've got a few questions. This is a purely made up example, so forgive any UI design quirks, and I'm leaving out a bunch of code just as the INotifyPropertyChanged stuff. 1. Let's say I've got a heavy-ish model class called Report. I'd like to create a Report browser type of viewer. I'd like the browser to have a combobox / listbox / treeview / something ...

OWA questions #2
A couple of questions I am having problems find the answers to: 1. Is there an easy way to change OWA to be at http://localhost instead of http://localhost/exchange 2. Is there an easy way to change OWA so that the domain name is not required when logging on (as though it is automatically passed)? 3. Are there any good, free, mailbox-to-web archivers out there? Thanks in advance 1. There are a few ways to have it where users type in http://<server>/, and get redirected to http://<server>/exchange. The easiest way is to do a redirection through the IIS console. The followin...

Stupid question
I've an XML document that has HTML data inside one of the tags (with < & > encoding as &lt; etc) I'm Transform()ing the document using output method="html", but I get the &lt; encoding instead of < > and friends. How do I make this work? If I just throw the HTML data (it's well formed) to the document and select the containing node's contents I get the text without any HTML Formatting. If I encode it using &lt; I get the same &lt; as I put in, instead of reversing it to < If I put it in a CData (using < & > OR &lt;) I g...

Re: OK!!!! TR installed
The BCM update for B2TR is now available at = http://www.microsoft.com/downloads/details.aspx?FamilyID=3D84cd8b36-4128-= 4c2f-bc02-c9564c0b2d6c --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 <glenn.nall@gmail.com> wrote in message = news:1158264579.992510.47040@d34g2000cwd.googlegroups.com... > On Vista 5600 >=20 > ran B2TR over Office and O...

Overpayment adjustment on loans & other questions
MS Money 2004 Premium Windows XP SP1 Problem 1) When creating a student loan in my debt reduction planner, I go through the wizard, setting the amount of the loan, interest, term of loan, payment amount & frequency. That all goes fine, but I have an overpayment adjustment I need to make that occured before my first payment due date. Unfortunately, money will not allow me to add the adjustment because it only manages the account starting on the date of the first payment. The adjustment goes 100% to the principal, and thusly affects the interest significantly, so that my whole plan is wa...

Two questions
1. Is there a way to make a header show only on page one? 2. I have a column that shows the grades students are in. I usually sort them by grade, but use K for kindergarten, so kindergarten always comes up at the end of the list, instead at the beginning where I want them. I need them to print out as K. Is there a way to make K = 0? If so, I would rather do this than label them all as 0 and have to change it to K when I print out my list. Thank you. B.Kundla 1. If it only appears on one page, it's not really a header. Just place it in the top of your worksheet (I use textboxes...

Re-Post-Refreshing Form with Multiple Forms and Fields
Would anyone be able to assist with this challenge. Thank you. I have a main screen form that allows entry to start from that location. It has four subforms and some other fields that are picked from other locations to filter the record. My challenge is it does not Refresh right away. I have tried putting Refresh and Repaint etc in the main form code but it still doesn't refresh. I put a Refresh button on it and that works but it's slow. Can anyone tell me what would be a more efficient method to get this screen to refresh right away. Thank you, Refresh and Repaint are n...

Question about Excel/Word 2000 Privacy
I know there was a NIC fingerprint embedded in office 97 document and it had been removed in office 2000. Now, does an exel/word 2000 document contain the information of my "user name"? The user name can bet set by Tools-->Options-->General-->User name or the User information tab in Word. If I send my document to my friend and he modifies it, will my user name still in the document? thanks! invalid, >Now, does an exel/word 2000 document contain the information of my "user >name"? Application.UserName gives you the name of the User who has the workbook...

Save Function and Macro Question
Some of my worksheets will ask me if I want to save upon exit even when I make no changes, and some don't. I believe it has something to do with a prior link that has been deleted. Also, a similar situation, upon opening a worksheet, the "macro warning" window comes up even though I have no macro. I started to write a macro and then deleted it. I still get the warning. These are just annoying. Some functions in excel are "volatile", they will re-calculate every time excel is opened, this will make the worksheet change even if you don't make any changes. Here are ...

Internet Explorer 6 Question
I know this is not the right newsgroup, but I am having problems with IE 6 opening links. It's not loading the pages. Does anyone know a group that could answer my question? Thank you. Ignore it. I've found a solution: http://www.anetforums.com/posts.aspx?ThreadIndex=3330 "WithoutAClue" <questioner@spam.com> wrote in message news:nykdd.4120$Cb5.31547@newscontent-01.sprint.ca... > I know this is not the right newsgroup, but I am having problems with IE 6 > opening links. > > It's not loading the pages. > > Does anyone know a group that cou...

a question on form.item
On furthering on my question from yesterday I just want to know how this collection is formed. Logic demands that form.item(0) would be the first form opened then if you open another form form.item(1) would be the ID of that form and if you opened another form from the that forms id would be form.item(2) then if you closed form.item(2) you would go back to the second opened form form.item(1) So based on this form.item(form.count-1) will always target the last form opened. Is this correct or does this collection mess itself up and this is not the case. Regards Kelvan I don&#...

Borderart Questions
Hello Folks For the first time, I want to use a borderart on a page that has a background. Unfortunately most borderart results in an ugly white line around the inner edge of the borderart itself. I do realise why the line is there on some of the borderarts, but why it is there on say 'swirligig' is beyond me. Is there way or workaround to make the white line disappear? Also, is there a Microsoft or other link to download more borderart. I realise it is possible to make some yourself but it always tends to look a bit rubbish and the same ones over the years tends to get a bit bo...

RE: What difference between inventory variance and adjustment
Hi, I am very new to Great Plains. We are using GP 7.5 and I am doing integration between GP and warehouse management system. I noticed there are inventory variance and adjustment transaction under Distribution Module and I need to take care of them. But, what is the difference between them? Thanks, Bing Thanks, JEK. Bing "JEK" <JEK@discussions.microsoft.com> wrote in message news:11CC8C10-26BC-49D3-9A71-EDD776615313@microsoft.com... > Adjustments are for increases or decreases to inventory quantities resulgint > from receiots or sales. Variances are used to ente...

MFC Newbie Question
I created an SDI application using the wizard. The view class is derived from CEditView. I want a pointer to my view class to call some member functions. So I added a member variable to the App class that was a pointer of type View. After doing this, I get compiler errors C2143 and C2501 in the header file of my view class where it declares the GetDocument(); function. I think I've got some kind of circular reference thing going on here but am missing something simple. class CTest2App : public CWinApp { public: CTest2App(); CTest2View* theView; // This is what I added snip }; ...

Re: Where is My Clipboard Data!?! Work Around to Transfer 56 Pages of Data From Clipboard=?ISO-8859-1?B?ig==?=
Thank You! Thank you: Very, VERY Much!! I found the Clipboard Data Object but it was Unreadable. I dragged the Microsoft Office v.X Folder to Applications from it's CD, Launched Word v.X, Right Clicked on a Clipboard Entry, Chose �Paste All� and Recovered 56 PAGES of Data!!!!!! Saved the Document, Closed Everything, Trashed the Office v.X Folder & Restarted. You Saved my A��, You Bet! Sea Mac Work Around to Transfer 56 Pages of Data From Clipboard� I dragged the Microsoft Office v.X Folder to Applications from it's CD, Launched Word v.X, Right Clicked on a Clipboard Entry, Ch...

VCalendar Parsing Question
This is dumb. I'm trying to think of a way to parse continued lines in a VCalendar file. The only indication that a line such as a description or a summary continues on the next line is that the next line begins with a space. I guess I'm just thick-headed today but I can't think of a good and fast way to add the following line to the previous line without loading the entire file as a blob instead of as a line input. A typical series of lines looks like this: BEGIN:VEVENT SUMMARY:Emancipation Proclamation UID:2008-05-08-11-02-18-63-@americanhistorycalendar.c...

Newbie question about Access and report
I understand that Access database can be created from Excel file. If you have an Excel file that has each row containing data pertaining to each individual, is it possible to produce a report where each page will contain the information for just that row and print it out? If you have a form each form contains data provided by individual for class that they would like to teach. The data is then converted to Access data base. Is it possible to use Access reporting to generate a printed page for each potential class. In other words if you have a table or spreadsheet in Excel and each ...

Money 2007 Question about tracking savings...
I am currently using MS Msney 2007 on a XP system SP2. My wife and I need to track money that we deposit into our savings account that will be spent for different things. We put in $150 every week but this needs to be split to different future bills (property tax, house insurance, christmas presents for the family, etc.) as well as savings goals like a new car. I have tried using the savings goal in money and have not gotten very far. Maybe I need to play around with it some more. I'm hoping that there is someone here who will be able to help me with. It is hard to look at our saving...

Re: How can I remove service from Outlook via command line?
Any thoughts on this? "Greg Brewer" <r3gbrewer@xspamhealth.nb.ca> wrote in message news:... > Is there any way to remove the Internet Mail service from Outlook via a > command line? I want to remove the Internet Mail service from all Outlook > clients in the corporation using a logon script or SMS package. Thanks. > > Greg > > Greg, The normal tools (Newprof.exe, Modprof.exe) for modifying Outlook 2000 profiles cannot remove services from a profile. So, you would need to create new profiles with just the required services. Hope this helps. -- Greg...

Customization beyond simple for Outlook Today....
I have a customer who REALLY wants to customize their Outlook today. I have done the minor things like adding background images and links to other things on it - but this one has me stumped. They want outlook today to come up with someone elses calendar listed there instead of theirs. I can change the Calendar link to the other person's calendar no problem... but the data it is pulling - well, I don't know how to tell it to pull from the other mailbox/calendar there. To put it into perspective, I have an administrative assistant who would want his bosses calendar to come up LISTED OU...

Simple Question?
I have a form with a command button connected to a query that returns a recordset in datasheet view. One of the fields is EmployeeID which corresponds to tblEmployee.EmployeeID which likewise corresponds to tblEmployee.FirstName and tblEmployee.LastName. As it stands, when I run the query, I have field EmployeeID in the returned recordset. I would like to have the employee's concatenated full name instead. Is this possible? Thanks, Tim Just write "Select tblEmployee.FirstName & " " & tblEmployee.LastName from tblEmployee" tbrogdon@gmail.com wrote: >I ...

Simple
Simple question. I have a whole bunch of Names in Col A, like 500. I have 10 names in Col B. I want Col C to look at each entry in Col B and see if it exist anywhere in Col A. Can someone help me with this? Thanks! =IF(ISNA(VLOOKUP(B2,$A$2:$A$500,1,FALSE)),"Does not exist","Exists") This assumes your data in in A2:A500. Place the formula in C2 and copy down. If the range is smaller, then you'll need to adjust it. HTH, Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Phillip Vong wrote: > Simple question. > > I have a whole bunch of Names i...

wab.exe non-readable after XP re-install.. HELP!
Hi, I recently had to reinstall my operating system (XP) and before doing so saved a copy of my address book as a wab.exe file in an external drive. After reinstalling XP and Outlook Express I tried to import the address book. But when using the import feature to view the back up file, it doesnt show any file at all. It doesnt recognize the saved wab.exe file. It appears invisable. But its there, when I look at the folder directly. I have extensive folders with 1000's of contacts in the saved wab.exe file and now cant seem to find any way to access it. I know its still fu...

Pre-purchase questions
I have a friend currently using Mac OS 9.2 but probably getting a new Mac within 6 months running OS X. He needs word processing primarily. Can the current releases of MS Word even run under OS 9.2? I see nothing on Microsoft's website about system requirements. He's not firmly wedded to the idea of MS Word as his word processor. Kind regards On 7/11/04 4:44 PM, in article 2a31501c46790$3c800170$a601280a@phx.gbl, "Morley Chalmers" <morley@7Office.com> wrote: > I have a friend currently using Mac OS 9.2 but probably getting a new Mac > within 6 months run...

A Worksheet Protection Question
How can I protect a worksheet so that any user can do anything desired, except change the formatting. Using Excel 2002. Thanks for any suggestions Paul Paul I've only got 2003 and 2007 on this machine, but I think this applies to XP (2002) Go to tools>protection>protect sheet and check format cells under 'allow users of this workbook to:' list Remember protection is a two stage process (locking the cells under format>cells...>Protection. this is the default and then protect the sheet, with or without a password) -- HTH Nick Hodge Microsoft MVP - Excel Southam...