How to find out what data is in an empty field

[bmacbmac]

Hello,

In SQL 2008 I am moving some data from one field to another. My source column is varchar(20) and my destination column is varchar(6).

Before copying the data I wanted to find any instances where the data is longer than 6 characters by running:

select * from Mytable1
where len(mycolumn) > 6

I had about 300 rows show up where mycolumn is empty. I included len(mycolumn) into my select statement and found that all of these entries are 15 characters long.

I want to convert these to NULL values and have tried:

update MyTable1
set mycolumn = NULL
where mycolumn = ''

update MyTable1
set mycolumn = NULL
where mycolumn = '               '
--15 spaces

But they each return 0 results. I have also tried copying the results from the grid results in query analyzer, but no luck.

Is there a way I can find out what is actually in this field so I can run updates against it? Also to find other possible instances in the table.

Thanks!

 

[PWise]

how about
update MyTable1
set mycolumn = NULL
where len(mycolumn) > 6

 

[gmmastros]

This is just a guess, but you should check to see if there are non-printable characters in the data. Specifically, you could have carriage return, line feed and/or tab characters (among others).

I would suggest that you set your output to text and run the select query again.

Right click inside the query window.
Click "Results To"
Click "Results To Text"

Now, when you run the query, the results window will show text instead of grid. Sometimes it is easier to see your data this way.

It's also possible that ALL of the characters are non-printable.

What do you get when you run this code?

select ASCII(Left(mycolumn, 1)) As ASCII_For_First_Character, * from Mytable1
where len(mycolumn) > 6

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

or
update MyTable1
set mycolumn = NULL
where Ascii(mycolumn) not between 65 and 126

 

[Olaf Doschke]

whitespace could also be SHIFT+Space = char(160). You could do a series of replace() to remove chracters 9,10,13,160,32 and then see, if strings end up empty.

Bye, Olaf.

 

[bmacbmac]

gmmastros - Thank you for the ASCII script. The ASCII_For_First_Character column showed 0 for each row. What does this mean? I'm not sure.

I tried the output to text and they all showed up blank.. no new info there.

pwise - I could do that, however, I would like to know what information is in this field and hopefully use this to identify issues or places where the character(s) may exist in < 6.

 

[gmmastros]

ASCII 0 is supposed to be a NULL character (not to be confused with NULL value).

Try this.

select SubString(mycolumn, 2, 100), * 
from   Mytable1
where  len(mycolumn) > 6

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks gmmastros. That column showed up blank (I did results as grid and text).

I also tried: select ASCII(substring(mycolumn, 2, 100)) and a 0 showed up for each row again.

I also did this:

select * from mytable
where ascii(mycolumn) = 0

and the exact same number of rows showed up.

I think I would be safe using that as my where statement in the update... what do you think?

 

[gmmastros]

Last try to find some data...

Select Convert(VarBinary(100), MyColumn) As ASCIIData
from   mytable
where  ascii(mycolumn) = 0

This time, you should see binary data in the output. Something like:

0x00004865

Each 2 characters after the x represents a character in the string. If all of your data is showing up as 0's (after the x), then you know you have a bunch of NUL char's and nothing else.

You could also try...

Select Replace(MyColumn, Char(0), '') As DataWithoutNulChar,
       Len(myColumn) - Len(Replace(MyColumn, Char(0), '')) As LengthOfDataWithoutNul
From   mytable
where  Len(myColumn) > 6

In this query, the ASCII 0 is replaced with an empty string. If there are any printable characters in the data, you should see them in the first column. If ALL of the characters in the string are ASCII 0, the 2nd column will be 0.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks gmmastros! Very helpful. I ended up with just a bunch of 0's after the X so I think I am golden.