Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Empty varchar is not null

Posted on 2009-05-19
9
Medium Priority
?
651 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 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