473,835 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Best way to force a varchar column to have no whitespace

I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Regards,
Terrence

Oct 2 '06 #1
5 2031
metaperl wrote:
I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.
Use an update trigger to rewrite it with any whitespace stripped out.
Oct 2 '06 #2
One method is with a CHECK constraint that checks for the list of your
prohibited characters. You may need to scrub data before adding the
constraint.

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyCo lumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
']%')
WITH CHECK
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"metaperl" <me******@gmail .comwrote in message
news:11******** **************@ e3g2000cwe.goog legroups.com...
>I have a column that I do not want any whitespace in whatsoever. I'm
wondering how do enforce this a DDL level instead of in 40 million
seat-of-the-pants after-the-fact computer programs accessing the
database.

Regards,
Terrence

Oct 2 '06 #3
On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:
>One method is with a CHECK constraint that checks for the list of your
prohibited characters. You may need to scrub data before adding the
constraint.

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyCo lumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) +
']%')
WITH CHECK
GO
Hi Dan,

I think you wanted to include NOT there:

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyCo lumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO

BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.

--
Hugo Kornelis, SQL Server MVP
Oct 2 '06 #4
I think you wanted to include NOT there:

Yes, thanks for the catch, Hugo. I also had an extraneous NO CHECK and '^'
in the expression. The corrected version:

ALTER TABLE Table1 WITH CHECK
ADD CONSTRAINT CK_Table1_Col1
CHECK (Col1 NOT LIKE '%[' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9) + ']%')
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hugo Kornelis" <hu**@perFact.R EMOVETHIS.info. INVALIDwrote in message
news:hh******** *************** *********@4ax.c om...
On Mon, 02 Oct 2006 12:26:16 GMT, Dan Guzman wrote:
>>One method is with a CHECK constraint that checks for the list of your
prohibited characters. You may need to scrub data before adding the
constraint.

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyCo lumn
CHECK (MyColumn LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) + CHAR(9)
+
']%')
WITH CHECK
GO

Hi Dan,

I think you wanted to include NOT there:

ALTER TABLE MyTable WITH CHECK
ADD CONSTRAINT CK_MyTable_MyCo lumn
CHECK (MyColumn NOT LIKE '%[^' + CHAR(32) + CHAR(13) + CHAR(10) +
CHAR(9) + ']%')
WITH CHECK
GO

BTW, nice catch on the various variations of white space - I was about
to propose CHECK (MyColumn NOT LIKE '% %') when I saw your reply.

--
Hugo Kornelis, SQL Server MVP

Oct 3 '06 #5
CONSTRAINT no_white_space
CHECK ( LEW(foo) = LEN (REPLACE (foo, ' ', '')))

You can then nest calls to REPLACE() for tabs,newlines, etc. easily.

Oct 3 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
7728
by: jacob nikom | last post by:
Hi, I would like to store XML files in MySQL. What is the best solution: 1. Convert it to string and store it as CLOB/text 2. Serialize it and store as byte array 3. Flatten it out and create one column per element, each column is VARCHAR Does MySQL has anything special for XML data? Is there any software which helps to store XML data in MySQL
1
1797
by: James Boulter | last post by:
Dear all, I have a database in which one column is titled 'force'. Now that this has become a reserved word, virtually no commands will execute, including of course a query to change the column name. Any ideas? Thanks in advance, James Boulter
5
29503
by: dmhendricks | last post by:
Greetings, I have a question. I work on some SQL2k/ASP.NET apps at work. My predacessor, who created the databases/tables seemed to have liked to use 'char' for all text fields. Is there a reason why he would have done this over using varchar? It's a minor annoyance to always have to RTRIM data and it makes directly making changes to the database more annoying (with all the pointless trailing spaces)? I usually use char for fixed...
7
4786
by: James o'konnor | last post by:
hello. i have the next for create one table into db2 CREATE TABLE "MYSQUEMA"."TABLADEMO" ( "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +0 INCREMENT BY +1 MINVALUE +0 MAXVALUE +2147483647 NO CYCLE
2
2536
by: Ben | last post by:
Right now I have 1 table. The first part is the first and last name along with address etc. There is about 10-15 fields here. The second part consists of times, penalties and if they enter this event or not. With 30 events max times 3 thats 90 fields. Although within limits even I know this is not the way to do it. I want to split the table into 2. 1 for the personal info and the other for the events. Not sure what the best way to link...
7
19798
by: Tzanko | last post by:
As we all know, there is a 8060 bytes size limit on SQL Server rows. I have a table which requires a number of text fields (5 or 6). Each of these text fields should support a max of 4000 characters. We currently store the data in varchar columns, which worked fine untill our appetite for text fields increased to the current requirement of 5, 6 fields of 4000 characters size. I am given to review a design, which esentially suggests moving...
6
12914
by: mike | last post by:
so I keep optimizing my fields down to the minimum character length necessary i.e., varchar(15), then I find out a month later its gotta get bigger, then a few months later, bigger again, etc. Nowadays on sql server 2005 and on, how bad is it really to use varchar(max)? Is there really a big performance or storage hit or is it negligible? -Mike
2
12059
by: Jeff Kish | last post by:
Hi. I've read up on this, and have something that works, but I was wondering if there is anything I'm overlooking with this. Situation is: I have a bunch of tables.. I need to modify table2 as part of an upgrade of a database schema.
3
7457
by: pedalpete | last post by:
I've just realized that one of my varchar fields which holds musical genres is full of whitespace. I've looked online and found a few references saying that text fields have whitespace but that varchar fields will strip the whitespace, but this does not seem to be the case. Strangely, my output looks like the genres are centered in the field (or maybe that is just how it display on output from command line mysql. Here is what the output...
0
9802
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10804
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10558
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10226
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9343
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6961
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5802
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4430
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3990
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.