Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Empty varchar is not null

Posted on 2009-05-19
9
Medium Priority
?
657 Views
Last Modified: 2012-08-13
I've been inserting a textarea field from a form into a varchar field.  This textarea field is empty most of the time, but the field in the database is not null.  When I run a len() on the fields that look blank I get numeric values ranging from 2 to 10, so there is some kind of data in there even though I can't see anything and the textarea was blank.

Going forward I am using validation on the front end to insert them specifically as null, but I'd like to be able to go clean up the table and make them all null because I'm now trying run a case statement off that field.
0
Comment
Question by:fairoakian
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24424687
Something like this, assuming the whitespace is spaces...

UPDATE SomeTable
SET SomeColumn = NULL
WHERE LTRIM(SomeColumn) = ''
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24424692
you could do this:

select * from table
where len(lrtrim(rtrim(fieldname))) = 0 or fieldname is null
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24424708
it must be some special characters
just try
 

SELECT LEN( LTRIM(RTRIM(urColumn))  )
from urTable

Also u can get the ascii value of first characters on those columns by using

select ASCII (urColumnName)
from urTable
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fairoakian
ID: 24426082
Thank you.  I tried all three solutions and the value returned is identical to using just the len function.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 480 total points
ID: 24426428
did u try this
select ASCII (urColumnName)
from urTable
0
 

Author Comment

by:fairoakian
ID: 24426637
Ya, it gave me a 13 for the empty fields.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 520 total points
ID: 24426894
>Ya, it gave me a 13 for the empty fields.
means you have a value starting with carriage return/line feed in your db :)
which is indeed neither empty nor spaces only.
you have to replace those characters eventually, and check in the application code to avoid this in the first place
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24426946
that seems to be strange
try this one two

select ASCII  (LTRIM(urColumnName) )
from urTable  
0
 

Author Closing Comment

by:fairoakian
ID: 31583142
Splitting points as aneeshattingal came up with the test and angelIII proposed the solution.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question