Solved

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

Posted on 2013-01-29
7
844 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 25

Expert Comment

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

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
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
Comment Utility
@jyparask : For every field
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 25

Expert Comment

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

Author Comment

by:sonawanekiran
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thanks @jyparask:
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now