?
Solved

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

Posted on 2013-01-29
7
Medium Priority
?
856 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 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