Solved

Empty varchar is not null

Posted on 2009-05-19
9
642 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 92

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
 

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 120 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 130 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now