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
2134 Views

Similar Articles

[PageSpeed] 3

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:

* RE: * Internet Mail Connection (SMTP) Setup
Chad Thanks! but is there nothing we can do to reject e- mails from unknown internal e-mail addresses and prevent someone to use the SMTP server to send e-mail internally? Also, anti spam solution will only filters e-mails but still delivers them (unless if we set it up to reject them, but then there is a possibility of legitimate e- mails). Again anti spam solution is not something we are looking for at this point. Any other ideas? anyone? Thanks. Paul >-----Original Message----- >Get a third party spam filter > >http://www.mailfrontier.com is a good one > >Thanks, ...

ThisWorkbook.Close question
Please take a look at the code below: Public Sub DumbFoo ( ) ThisWorkbook.Close() Sheet1.Range("A1").Value = -1 ThisWorkbook.Save() End Sub Is it safe to assume that the code below the "ThisWorkbook.Close()" line will NOT be executed??? My question is basically: Does all VBA code stop executing once a .Close() function is called??? Thank you! I don't believe the code would execute at all because of the parentheses after Close. It would throw an error an tell you that something is missing, or type mismatch. But, if you did...

Data Validation Question #5
The "Input Message" that can be added to Data Validation, has someho become dissassociated with the cells containing the Data Validation. In normal operation when you select a cell containing DV, the "Inpu Message" appears next to and slighty below the selected cell and the moved as you selected different cells, however on my sheet thos messages appear in a spot well away from the selected cell and don' move when you select a different cell. Is there some way to "re-attach" them to the cells short of removin the DV and putting it back. Thank -- Case -----...

Fwd: Re: MS Money
Apologize if this is double posted... I sent this message yesterday and it still has not shown up on my news server today, so I'm sending again. bj -------- Original Message -------- Dick Watson wrote: > The OEM Works product may differ from the retail one and the OEMs may be > "free to install it their way". > That's what I guessed after reading that last response quoting MS about the "Works Suite". > At any rate, Money Standard pretty much stopped existing somewhere along the > way and was replaced with the incredibly...

pivot table error/question
I have a pivot table which has interest rate as one of its fields and the data is an amount. if I use a cell to the right of a my table and link it to lets say the total column within a given interest rate, it gives me that total correctly using the "getpivotdata" function for all but one of the rates. The one cell which doesnt work right gives me the #ref error. I've been researching the help menu for this function. Neither of the examples of causes of that error are the case here. I dont know if this will be possible to answer without seeing the sheet (its large, ...

Re: New to Access
Correction >asked you'd rather join line you can also pull a couple of fields from either table into the query builder grid. when you run this query you'll get matches between both tables. My spell checker munched the above again it should read : after you draw the join line, you can also pull a couple of fields from either table into the query builder grid. when you run this query you'll get matches between both tables. ...

Waitable Timer Question Please help!
Hello I am trying to use the waitable timer in a MFC project and using MsgWaitForMultipleObjectsEx() to wai on the timer handle. But my timer callback doesnot get executed. Below is the source code with al the error checking removed. Please help me to fix this problem //Global int wLoopCount= 0 void CTimerTestDlg::OnStartTimer() HANDLE hTimer[1] LARGE_INTEGER liDueTime int breakLoop = 0 int retVal = 0 // Create waitable time hTimer[0] = CreateWaitableTimer(NULL, TRUE, "WaitableTimer") // start the timer 5 sec from now and tick every 2 se liDueTime.QuadPart = -5000...

DeleteDC() and DeleteObject() Question
Hi guys, Will DeleteDC() and/or DeleteObject() fail if neither have been Created() ? Is there a way I can test it, like this: CDC memDC; // we didn't create it yet if(// was it created ?) memDC.DeleteDC(); Same for a CBitmap except DeleteObject() is used to delete. Thanks, Robert A. "Robert A." <death@invalid.org> wrote in message news:eDl2JuwTEHA.1472@TK2MSFTNGP12.phx.gbl... > Hi guys, > > Will DeleteDC() and/or DeleteObject() fail if neither have been Created() ? > > Is there a way I can test it, like this: > > CDC memDC; > // we didn'...

Mailbox Manager Question #5
Exchange 2003, I have several policies set up in Mailbox Manager, when it runs it looks like I get an separate email report on each policy. For several months now, only a couple report shows things are being deleted, the rest are all 0. Some of the retention policies are only 7 days, so something is wrong. My question is, how can I tell which email report goes with which policy? There doesn't seem to be a clue in the message as to what policy it's for. ...

Excel for Mac sort question
Is there an add-in or easy way to sort by seven columns rather than by just three columns? Nope. You could sort 3 times in reverse order of importance. Or maybe you could record a macro when you do the 3 sorts and then just rerun that macro when you need the same sort. I don't use Mac's. Can you still record macros in your version of excel? garagecapital wrote: > > Is there an add-in or easy way to sort by seven columns rather than by > just three columns? -- Dave Peterson Hi, Here are 3 solutions if you don't want to do use a macro: 1. Sort the three least i...

Outlook 2003
I went to the following website, http://www.gaznet.au.com/spam/download.htm and downloaded the anti-spam domains file, and copy pasted it into the outlook spam filter. Now it takes for ever for it to sort the spam out. Is this normal? ...

RE: Install the correction update from the Microsoft
--cdfjsysqlcdpu Content-Type: multipart/related; boundary="lgtoqnldawbpqmrot"; type="multipart/alternative" --lgtoqnldawbpqmrot Content-Type: multipart/alternative; boundary="oiaosghhnqjyoimw" --oiaosghhnqjyoimw Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilit...

Preparing for complete network upgrade
Ok, i have done this a couple of times in the past where i've taken a 4 day weekend to basically clean install on both server and clients the latest OS's -- i'm about to do it again, but was thinking of an easier way to do it this time around...i would like to start off by clean-installing the clients first and making sure they were fully patched, AV'd and then putting them back into the SBS 2003 network until all of them were done - then bringing the server up to SBS 2008 after i'd done all of the clients... The only drawback i can think of is if in the past 5 ye...

General Question About Accounts
This may sound very dumb but I am having a problem getting my individual accounts to balance. For example, I purchased textbooks for $100, but later i received a credit in the amount of $50 as a refund. I use the education:textbooks category for the initial expense and thought I could use the the same account for the refund (just saving as a deposit rather than a expense) Shouldn't this offset the original balance? For some reason the credit is totaled into the total expense. This is evident when I open the category to see the transactions. Any ideas as to why, or even better...

Simple hack to get $600 to your home. 06-05-10
Simple hack to get $600 to your home at http://dailyupdatesonly.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. SUKANYA wrote: > Simple hack to get $600 to your home at XXXX://dailyupdatesonly.tk > > Due to high security risks,i have hidden the cheque link in an > image. in that website on left side below search box, click on image > and enter your name and address where you want t...

Exchange 2007 disk space question
Hi, We have around 500 mailboxes and our Mailbox database is getting very large. We may soon be running low on disk space on the server. The server is attached to a SAN but the SAN is pretty much full. We cannot asign more space to the drives where the database store sits. We are using LCR. Can we move the store to another network location or does the disk have to be attached to the exchange server. Does moving the mailboxes to a newly created store really get much space back. When a consultant did our migration from 2003 to 2007 a couple of years ago I did not notice mu...

XML updating,querying question #2
I use a TreeView WebControl Menu which i installed from microsoft. But the TreeView XML that can be loaded with the Webcontrol is very specific.For example (The root as to be TREENODES!!):- <?xml version="1.0" encoding="utf-8"?> <TREENODES> <TreeNode Text="Contracts"> <TreeNode Text="Contract Pads" NavigateUrl="a.aspx" target="_blank"/> <TreeNode Text="Points Deduction Report" NavigateUrl="a.aspx" target="_blank"/> <TreeNode Text="" NavigateUrl=...

Factorization Question
Is it possible to calculate the following progmatically or with a formula? (x^2+7x-30) Thanks in advance For a given value of X you can calculate the value of x� +7x -30 with =A1^2+7*A1-30 where A1 holds the value of x But this is not factorizing. Please clarify " to calculate the following" best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Paul" <psl2005@hotmail.co.uk> wrote in message news:uYN98lXhJHA.1288@TK2MSFTNGP02.phx.gbl... > Is it possible to calculate the following progmatically or with a...

worksheet change question 04-20-10
As I scroll across a particular spreadsheet, I would like to have the following columns change width, and zoom, as shown in the code below. However, the results are erractic. The zoom only works on the last column, and all the widths are 20. How do I correct this situation so each column behaves as I want? Thanks, Tonso If Target.Column = 7 Then Target.Columns.ColumnWidth = 20 ActiveWindow.Zoom = Sheets("Notes").Range("c28") 'c28 = 100 Else Columns(7).ColumnWidth = 3.33 ActiveWindow.Zoom = Sheets("Notes").Range(...

Date Format Questions
how do I get this =((DATE(2005,12,30))&" YTD") to look like this Dec 05 YTD Right now I have a Cell that look like : 38716 YTD Hi =TEXT(DATE(2005,12,30),"mmm dd")&" YTD" Arvi Laanemets "Mestrella31" <mestrella@mxn.com> wrote in message news:F523D264-3CC1-40E0-A371-1D1C21D8F4F7@microsoft.com... > how do I get this > =((DATE(2005,12,30))&" YTD") > > to look like this > > Dec 05 YTD > > Right now I have a Cell that look like : 38716 YTD or you could just format the cell as mmm dd \YT\D -- H...

Re: Help with variable percent
Would someone please point me in the right direction? How do I begin to write an excel file which will spread a number, using different percents, among days of the week? The number of days will vary from week to week. The percentages will always total 100% for whatever number of days are in the week and will always change with a change in the number of weekdays. Please help with some ideas, as I need to change this sheet. Do I need to build a matrix? I am hoping to create a key word or number that when input will activate this sheet properly. Is this possible? Tnx, Cy Cy This sounds ...

Custom Button Question
Greetings, I made a custom toolbar button to run a macro. Is there any way to export it so I can let others use the button face? Thanks for your help! Ray Hello Ray, You can export either a CommandBarButton or ToolbarButton picture usin the CopyFace method. This will copy the button's face to clipboard. Fro there it can be pasted wherever you like. Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1846 View this thread: http://www.ex...

National Language
I'm looking through the National Language Support documentation but don't think what I need can be done automatically. Let me explain : On our engineering drawings there are often several sectional views and these are labelled A-A, B-B etc. So in our code which automatically generates these sectional views there is a starting dialog which has a box for the section label ("A" in this case) and the code increments the next character until "Z" and wraps round to "A" again. (Highly unlikely that there will be more than 26 sections on one drawing but t...

Simple but I cannot....
Can I make it any simplier. I want to make a tracking form. The first col. A is customer name and address,Col. B is date of purchase, Col. C is method of payment, Col. D is item, Col. E is Amount and Col. F is total....total is the Amount added to the last amount in the Total, so the total goes up with each Amount entry. Well, I cannot get this to work. In Col F, I type a formula =Total+Amount. Nothing happens, except sometimes when I type this in the space, the heading on the column comes out #Name. This thing is driving me crazy. Are there instructions that can help me with a most...

CRM 3.0 Questions
Could someone please point me in the right direction to receive answers regarding Microsoft CRM 3.0 questions? Some questions we have are, when and where can we receive the CRM 3.0 Beta (Partner) version? If we already have CRM1.2 installed, would v3.0 be installed on top of this instance or is it a separate install? Thank you in advance for your time and assistance. MS CRM Beta 3.0 is suppose to be posted tomorrow to the partner site.. BUT be warned you do not want to upgrade 1.2 to 3.0 until the official release. In fact any version of 3.0 prior to the official release will not b...