Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Empty varchar is not null

Posted on 2009-05-19
9
Medium Priority
?
653 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…

885 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