asked on

Empty varchar is not null

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.

Patrick Matthews

Something like this, assuming the whitespace is spaces...

UPDATE SomeTable
SET SomeColumn = NULL
WHERE LTRIM(SomeColumn) = ''

chapmandew

you could do this:

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

Aneesh

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

fairoakian

ASKER

Thank you. I tried all three solutions and the value returned is identical to using just the len function.

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fairoakian

ASKER

Ya, it gave me a 13 for the empty fields.