Solved

Empty varchar is not null

Posted on 2009-05-19
9
646 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 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
Technology Partners: 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 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 143

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Two tables - Sum of values - What is the difference 31 56
ms sql + top 1 for each customer 3 65
sql query help 2 62
Applying Roles in Common Scenarios 3 19
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

733 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