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

Similar Articles

[PageSpeed] 49

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:

Mell Install Question
We have MELL Installed on a Web Server. It is working fine and we have several modules up and running and will be adding more and more.. When we first installed eLearning, we said 'NO' to the SQL Server peice of the install. Now we'de like to have it log to SQL Server but am not sure how to go about doing this. reun the install? uninstall and reinstall?? I know there must be a way to implement SQL Server without having to reinstall the whole thing !!!! Can anyone help? ...

Simple graph
I have a chart with zip codes and a population rate. ZIP RATE 80001 2.34 80002 1.23 80003 1.23 I want to make a chart that has the rate on the x axis and the number of times that rate occurs on the y axis. | | | * | | * |_________________________________ | | 1.23 2.34 Thank you. I will assume the ZIP and RATE stuff is in A1:B200 (labels in row 1) Label in G1 to read RATE (but leave it empty for now), label in H2 to read COUNT Make a list of rates in G2:G20 (say) In H2 =COUNTIF($B$2:$B$200,G2) Copy down the row Select H1:G20 and make a...

Exchange question #2
I have just taken over a medium size office, running Exchange 6.5 on Small Business Server 2003, clients run Outlook 2003. My immediate problem is I have two users who cannot send email to several specific addresses outside the company. Everyone else can send to these addresses. When these two people send email to the outside addresses, they immediately get an email back stating that the address doesn't exist or something to that effect. It appears to be bounced right back from the server. Does anyone have an idea here? Thanks in advance. Can you post the NDR? -- Bharat Sune...

Drop down. *EASY* question!
BlankHiya everyone :) I was just wondering if anybody could explain something reeeeally easy for me? In Excel, I would like a two drop-down features in my columns of my spreadsheet. The first one (columnE) with the options 'yes' or 'no'. The second one (columnD) with a list of names, eg. Anthony Peter Mark Jonathan Thankyou for ANY help you can give me! -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" Data>Validation, choose a type of list, and then just type the values comma separated. -- HTH RP (remove nothere from...

Simple help with implementing Outlook-like GUI
Hi All. I am an experienced unix programmer who sometimes has to do something in the windows world, and always has newbie-questions. Basically I want to write an application program that looks like outlook in that it has nice icons down the left hand side that choose the content of the main area in the right hand side. So I started the MFC App wizard, and got me a SDI program with a CLeftFrame (CTreeView) and a CMainFrame (CFrameWnd). Then I prepared the following snippet of code to draw the buttons: // Create a pushbutton CBitmapButton* pmyButton; pmyButton = new CB...

Simple Simple Simple
Just started Excel (again) and need to make an easy speadsheet fo calculating square inches. Column A: Height Column B: Legnth Column C: Total Tags per 16 x 24 Column D: Cost per tag Figures I know: each sheet cost me $9.98. each sheet is 16 x 24 inches What I want to do is enter the Height, enter the Length and have th total (sum) be entered into columns C and D. I can't believe I forgot how to enter formulas. Any help will b appreciated. Thank you, Crai -- Message posted from http://www.ExcelForum.com Hi Craig, One Way, might be better ways but this'll work: List the colou...

Experts Question
Hi. I'm writing a multi tool application, something like outlook. I have an MDI application of course. Inside the MDI the user may choose any of few Mini tools (something like calendar,contacts,mail, tasks etc). A mini tool may be an MDI - e.g. one of my mini tool is a resources editor, which means that the user may first choose the "Resource Editor" mini tool, and open/create many windows to edit. Another mini tool will be "Scripts Editor", which the user may choose to open many scripts views. My problem is how to implement the switching between mini tools, I wanna pu...

Simple hack to get $500 to your home. 06-05-10
Simple hack to get $500 to your home at http://uknews.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. ...

Install question
Hello, can I have SQL Server 2008 Express with advanced services with SQL Server 2008 Enterprise Evaluation installed on the same machine ? Thank you Gilles On Fri, 30 Apr 2010 07:49:10 -0700 (PDT), Gilles Koffmann <gilles.koffmann@gmail.com> wrote: >Hello, > >can I have SQL Server 2008 Express with advanced services >with SQL Server 2008 Enterprise Evaluation installed on the same >machine ? > >Thank you >Gilles Hi Giles You should be able to do this, have you tried and failed? John ...

xml/rid question
I am using Visual Studio 2003/C# I created a new xml schema/xsd file (Project Create new item->Dataset) I then added Elements to the schema using the designer From the toolbox I then created an instance of my dataset. I then dropped a grid (Infragistics UltraWebGrid) onto my form, and bound the grid to my dataset. At runtime, I configure the columns of the grid, get some records from the Employee database (SQL Server2k), and add some rows to the grid. The user then enters some data into various cells of the grid. I am now ready to update the database with the contents of the grid. What ...

Simple Query Wizard hangs
I am using Access 2003 When I initiate the "Simple Query Wizard", I am presented with the first screen - "What fields do you want in your query" When I click on the pulldown list to select a table, I get the following message: "The expression On Get Focus you entered as the event property setting produced the following error: the text you entered isn't an item in the list * the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. * There may have been an error evaluating the function, event, or macro&q...

Xch2k (SB edition) LOG and IFS question
Dear, Even I did the full backup with the Exchange server, the LOG and IFS extension file still in the MDBDATA folder. How to purch? Thanks!! -david I'm not sure what you are referring to regarding the IFS extension file. Please explain. As far as your full backup, how did you perform the backup? It must be done using an exchange-aware backup program (such as NTbackup run from a computer with minimum the Exchange management tools installed). It must also be a full *online* backup, with all Exchange services running and all databases mounted. -- Ben Winzenz Exchange MVP M...

Several Outlook Questions
I have Outlook 2003 with Service Pack 3 installed on my desktop and laptop. First, I would like to syncronize my contacts and all my mail folders: Inbox, Sent, etc. on both computers so that I would have that information on both computers. Second, I would like to store all of my Outlook data--contacts, folders, etc on my drive C: so that when I reconfigure my computer I don't have to backup and restore this data which is such a hassle. Can I do this and how? 1) http://www.slipstick.com/outlook/sync.asp 2) By default the outlook data file is on your C: (Windows drive) Backup, wha...

WDF Interrupt question
I'm sorry I just start learning windows drivers development. I'm looking at Interrupt servicing in drivers in WDF. WdfInterruptCreate create the interrupt object but where do we put the information about interrupt vector? I noticed we can get vector by using WdfInterruptGetInfo, but where do we set it? I think I have found where it comes from. From Hardware Resource list, which is filled by PnP Manager. On Mar 22, 12:56=A0am, mpefareo <mpefa...@discussions.microsoft.com> wrote: > I'm sorry I just start learning windows drivers development. I'm looking ...

Re: NDR
(Sorry, I hit Send when aiming for Edit | Paste!) "Boris Nikolaevich" <boris@nikolaevich.mailshell.com> wrote in message news:... > Hi, > > I will try to give enough details to be helpful without overdoing it. If I > leave anything out, please reply and I will provide more information. > > - We'll call the domain "boris.com" just for reference. > - Running Exchange 2003 on Windows 2003. > - Besides the Exchange server ("exchange.boris.com") there is a > "mail.boris.com" mail server hosted by our ISP > - DNS at th...

I Need a Simple Sales Software
Hello, I am looking for software to keep track of my prospects, customers, and the notes of the sales process. I spend about one hour a day on sales and marketing, so I prefer a simple and inexpensive software. Maximizer, Outlook, Act, and Goldmine seem made for the full-time professional salesperson. Please let me know if what I am looking for exists. Best Regards, T.I. ...

Make it more simple or intuitive to do simple things
I appreciate the fact that applications are becoming more versatile and able to do things that we hardly thought possible in the past but I feel that in this added complexity you are losing sight of the need to do simple things easily without resorting to trial and error or consulting "help" which often anyhow doesn't lead one straight to the solution! An example is how to produce a chart with a series of months i.e Jan Feb Mar etc appearing on the X axis. This is no doubt something that resulted naturally in the first versions of Excel charts or in a competitor's ea...

Question about using the right or left formula
How do you get excel to recognize the data as a number when using the right or left formula? For example, cell L73 has the following formula: =RIGHT(D3,5) The data this equation produces is +137. I have another cell that has a formula that says =IF(ISNUMBER(L73),L73,"NO"). The answer I get is NO. I should get +173. I have formatted cell L73 to be a number. How do I fix this formula? Thanks Being a text function, the right function will return text. To cause Excel to perform an implicit type change from text to numeric try one of these: =--RIGHT(D3,5) =RIGHT(D3,5)*1 D...

filtered list question
I want to autofilter a list and then run a vlookup based on the criteria used. How do you reference the first cell of a filtered list? Assumptions: A2:C10 contains your data E1 contains your lookup value You wish to return the corresponding value in Column C Formula: =VLOOKUP(E1,IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-MIN(ROW(A2:A10)),0,1) ),A2:C10),3,0) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the formula accordingly. Hope this helps! In article <B218E24D-73A1-4BCE-8B8A-2A52DD6074BE@microsoft.com>, "Bob B" <Bob B@discussions.microsoft.com...

CImageList and CImage question
I want to use an image list with a list control. I am using CImage to load a bitmap file (actually several files) like: CImage image; image.Load("test.bmp"); How do I add this image to a CImageList, which can then be used by the list control? -- Regards, Frank This looks ugly but seems to be the only way. CImage image; image.Load("test.bmp"); CBitmap Bmp; Bmp.Attach(image.Detach(); m_ImageList.Add(&Bmp,RGB(0,0,0)); Bmp.DestroyObject(); image.Load("test2.bmp"); Bmp.Attach(image.Detach(); m_ImageList.Add(&Bmp,RGB(0,0,0)); Bmp.DestroyObject(); .... ...

Curious Database Query Question
When creating a "New Database Query" from another Excel workbook, when you get to the "Select Workbook" dialog, there is a small checkbox labelled "Read Only". According to the help file, it has this to say: "To prohibit updates to this file, select the Read Only check box." I don't understand why this is an issue. In my understanding, database query is a one-way transfer of data from the external source to your excel workbook...if you edit the data in your workbook, that *doesn't* change the data back in the external source...right? Am I wr...

Simple public folder permission problem
I have a public task list folder. However even though I have given everyone "Author" priviledges and full control of the directory, users cannot update the tasks (although they can create new ones). The changes to tasks get reset to their original values. Where should I look to fix this problem? Brian How did you grant Author Access, via Outlook or ESM? What do you meanyou gave them full control of the directory? "Brian Taylor" <taylorb@newsgroups.nospam> wrote in message news:e2hwumGrEHA.1160@tk2msftngp13.phx.gbl... > I have a public task list folder. Ho...

SOP process question
Hi, We use SOP and have a new process that I'm not sure how to handle. We receive an order from a customer for a year's worth of product. We need to book the order to show a quantity of 12 but bill and fulfill 1 item monthly. Is there any automated way to fill the order monthly (decrement the inventory by 1 every month) and invoice without having to go in to every order? I looked at using recurring orders but that does not give us the line item with a quantity of 12 for reporting....I feel like I'm missing something very basic. Thank you! Yes and no.... ...

What is simple text?
When creating a custom list that includes a column of accounting formated data, I get an error message that 'fields without simple text will be ignored'. The result is all other data except the accounting formatted data. Help!!!! I suppose it means without the currency symbol and thousands delimiters. On Fri, 8 Apr 2005 11:01:04 -0700, TexMas <TexMas@discussions.microsoft.com> wrote: >When creating a custom list that includes a column of accounting formated >data, I get an error message that 'fields without simple text will be >ignored'. The result is ...

IMAP question
One of my email accounts is set as IMAP. When I open outlook it connects to the account and checks email. However, if it is diconnected from the account for some reason, it would not connect to the account again (even if I try file connect to...). I have to close outlook and to open it again to get connected again. How can I solve this problem? ...