[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Global replace word in SQL Notes field

Posted on 2011-10-03
15
Medium Priority
?
281 Views
Last Modified: 2012-05-12
How would I replace the word NULL in a 2005 SQL table notes field with blank.


Any ideas?  Thank you
0
Comment
Question by:goldmineexpert
  • 5
  • 2
  • 2
  • +3
15 Comments
 
LVL 3

Accepted Solution

by:
BartVx earned 501 total points
ID: 36904336

If what you want is to replace the NULL VALUE with an empty string:

UPDATE <yourtable> SET <yourfield> = '' WHERE <yourfield> IS NULL;

If you want to replace the actual text "NULL" with an empty string:

UPDATE <yourtable>SET  <yourfield> = REPLACE( <yourfield>, 'NULL', '')
0
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 498 total points
ID: 36905359
You wanna replace NULLS by an empty string in the table or only in the select view?

it's not really ya good idea to replace it with empty fields until you really have to!!, you can always use ISNULL function to display an empty string.

SELECT IsNull(ColumnName, '')   FROM TableName
0
 

Author Comment

by:goldmineexpert
ID: 36905673
Actually the word NULL is in the notes and I would like to wipe out the word NULL.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36905725
Not a good practice...for many reasons, you can search it out

http://decipherinfosys.wordpress.com/2009/03/27/null-vs-empty-zero-length-string/

Just use    IsNull()   or   COALESCE() functions in the presentation layer and keep the NULLS there :)
0
 
LVL 3

Expert Comment

by:BartVx
ID: 36908198
As I understand it, the word NULL is somewhere within the other text that is in the notes field, correct?

Ex: 'This is a note about the NULL current record'

If this is the case, and you want to update every record this way,  use my second suggestion:

UPDATE <yourtable>SET  notes = REPLACE( notes, 'NULL', '');
 
0
 

Expert Comment

by:ZappaGabriel
ID: 36911768
Well, the real value of your field is NULL,
But if you want to see a blank in a Query you must use the following sentence:
Select ISNULL(FieldName,"") From TableName
If you want to replace the value in your table then use the following sentence:
Update TableName Set Fieldname = ""
0
 

Assisted Solution

by:ZappaGabriel
ZappaGabriel earned 501 total points
ID: 36911792
Well, the real value of your field is NULL,
But if you want to see a blank in a Query you must use the following sentence:
Select ISNULL(FieldName,"") From TableName
If you want to replace the value in your table then use the following sentence:
Update TableName Set Fieldname = ""  Where FieldName is Null
0
 

Author Comment

by:goldmineexpert
ID: 36915227
When I used this statement I got the following results. Any idea?

UPDATE <yourtable>SET  notes = REPLACE( notes, 'NULL', '');


Msg 8116, Level 16, State 1, Line 2
Argument data type image is invalid for argument 1 of replace function.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 36915367
Since 8.52 they changes the NOTES field to an IMAGE type!!! considering it was being depreciated they did not think

to set all NULL values correctly for this field try

update CAL set NOTES = 0x00 where NOTES is null

Open in new window

0
 
LVL 3

Expert Comment

by:jvejskrab
ID: 36917003

select convert(image, replace(convert(varchar(max),convert(varbinary(max), s) ), 'NULL', ''))
from <yourTable>

why you store text in image datatype???
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 36917054
FrontRange decided to use it as a binary field. They made a bad choice.
0
 

Author Comment

by:goldmineexpert
ID: 36940794
When I ran this:

select convert(image, replace(convert(varchar(max),convert(varbinary(max), s) ), 'NULL', ''))
from cal

I got this error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 's'.
0
 
LVL 3

Expert Comment

by:jvejskrab
ID: 36940907

You have to replace 's' by the column name in the table "cal" containing the image data
0
 

Author Comment

by:goldmineexpert
ID: 37032506
I kind of put this on the back burner but will be testing today.

I will keep you updated of the progress.

bob
0
 

Author Closing Comment

by:goldmineexpert
ID: 37269711
no resolve
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

873 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