goldmine NULL in history

When converting Goldmine 6 to 9 (6 to 8.5 to 9.2) each history and cal notes where stamped with the word NULL. If there was data in the notes it was retained.

I would like to remove the word null from the notes using MSSQL or ?

Any ideas?  Thank you
goldmineexpertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GMGeniusCommented:
It shouldnt matter if NULL is in that field, its classed as empty
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
goldmineexpertAuthor Commented:
Customer sees the word NULL in all history and cal records which had empty notes.

Any ideas to remove the word NULL from the notes ?

Thanks, bob
0
GMGeniusCommented:
As posted on your other question

update CONTHIST set NOTES = 0x00 where NOTES is null

Open in new window


You can use this for any other table that has a Image type field
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

stevengraffCommented:
Would this work as well?


update conthist set notes = '' where Notes='NULL'

Open in new window

0
GMGeniusCommented:
No. Null is a state not a value
0
GMGeniusCommented:
And the note field is an image so you cannot set a string value
0
stevengraffCommented:
Right... I was just trying to account for the fact that the users are seeing that word through the UI, as if maybe it was actually written into the db that way.
0
goldmineexpertAuthor Commented:
I believe the word null is written as a word.

This did not work by GMGenius when I ran this:
update CONTHIST set NOTES = 0x00 where NOTES is null

Got this error when I ran this:
Msg 402, Level 16, State 1, Line 1
The data types image and varchar are incompatible in the equal to operator.

Any Ideas?
0
stevengraffCommented:
First, just to be sure you're identifying things properly, run this query:
 
select  cast(cast(NOTES as varbinary(max))  as varchar(max)) Notes from conthist
where rtrim(cast(cast(NOTES as varbinary(max)) as varchar(max))) like 'null%'

Open in new window


The assumption is that the word Null appears at the beginning of the notes. True?

Next, this query should expunge the word 'Null'
 
update CONTHIST
set NOTES =
REPLACE(rtrim(cast(cast(NOTES as varbinary(max)) as varchar(max))),'NULL','')
where rtrim(cast(cast(NOTES as varbinary(max)) as varchar(max))) like 'null%'

Open in new window


It's not case sensitive.
0
goldmineexpertAuthor Commented:
I kind of put this on the back burner but will be testing today.

I will keep you updated of the progress.

Have you experienced any of this during your upgrades? I see it often.

bob
0
stevengraffCommented:
Have you experienced any of this during your upgrades? I see it often

I've seen it, but only on one or two... I don't have a sense of what's causing it, but it is an odd feature.
0
goldmineexpertAuthor Commented:
still no resolve
0
stevengraffCommented:
@goldmineexpert... what do you mean "still no resolve?"

The solution I posted for you should work just fine! Did you not try it? If you leave the database with the word NULL in the notes field... well, yes, that's unresolved work you still need to do. But I'm not hearing that you tried my solution and it failed (which I promise you it would not!)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Contact Management

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.