Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server : Table has blank values in some fields and need to replace with 'NULL'

Posted on 2013-01-29
7
Medium Priority
?
859 Views
Last Modified: 2013-01-29
I have table which has blank space in some fields. I need to replace with NULL i.e. instead of blank spaces I need null values
0
Comment
Question by:sonawanekiran
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38830766
update mytable set mycolumn = null where len(ltrim(rtrim(mycolumn))) = 0
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38830769
Hi,

Do you want to check specific fields, or do you want to check for every field of the table?

Giannis
0
 
LVL 17

Author Comment

by:sonawanekiran
ID: 38830797
@jyparask : For every field
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38830804
Modify my above statement to include your list of fields and it'll work just fine.
0
 
LVL 17

Author Comment

by:sonawanekiran
ID: 38830807
Or better way if I can do like this..

Select "name" -> if name fields is blank then select null
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 2000 total points
ID: 38830913
Hi,

The code bellow is reusable and it takes as a parameter the table name.

It searches for string type fields and updates them. I hope it gives you an idea of what to do if you want to search for other datatypes.

This is for view only. If you want it to actually do some work you may replace the 'PRINT' with 'EXEC'

DECLARE	@TableName VARCHAR(100)
SET		@TableName = 'YourTableName'
DECLARE	@TableField VARCHAR(100)

DECLARE UpdateTableFields CURSOR
FOR		SELECT	C.Name
FROM	SYS.OBJECTS O
		JOIN SYS.COLUMNS C
			ON C.OBJECT_ID = O.OBJECT_ID
		JOIN SYS.TYPES T
			ON C.USER_TYPE_ID = T.USER_TYPE_ID
WHERE	O.NAME = @TableName
		AND T.Name IN
			(
				'text',
				'ntext',
				'varchar',
				'char',
				'nvarchar',
				'nchar',
				'sysname'				
			)

OPEN UpdateTableFields

FETCH NEXT FROM UpdateTableFields 
INTO @TableField

WHILE @@FETCH_STATUS = 0
BEGIN
	
	PRINT
	(
		'UPDATE ' + @TableName + ' SET ' + @TableField + ' = NULL WHERE ' + @TableField + ' = '''''
	)

    FETCH NEXT FROM UpdateTableFields 
	INTO @TableField
END 
CLOSE UpdateTableFields
DEALLOCATE UpdateTableFields

Open in new window


Giannis
0
 
LVL 17

Author Closing Comment

by:sonawanekiran
ID: 38831134
Thanks @jyparask:
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

618 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