Replace null values in db atble

[magmo]

Hi

Is there a way to replace null values in different columns in a database table with ''?

I know I can run a update on the table, updating each column. But I would like to it in one action

Regards

 

[fredericofonseca]

something like

update tbl
set myfld = isnull(myfld," "), myfld1 = isnull(myfld1," "), myfld2 = isnull(myfld2," "),
where myfld is null or myfld1 is null or myfld2 is null (this bit not required but advisable)

Now I don't see the need for you to remove NULL's from the fields. Care to explain why you need that?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[magmo]

Hi


Thanks for the help, the reason I need this is that I later run a procedure that compare 2 tables and check if data is updated or new. And the procedure doesnt work if the destination table hav null values...


Regards

 

[fredericofonseca]

then it is the procedure code you need to fix, not the data.

If you show us your procedure, we will be able to advise you on what needs to change so you can handle NULL values on the fields.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[magmo]

Allrighty, here it is...

CREATE procedure sp_Compare (
				@TableA varchar(50),
				@TableB varchar(50),
				@ExcludeFields varchar(255),
				@JoinFields varchar(255),
				@CompType int,
				@debug bit = 0
				)
as
begin
	--Author: 	Subhan Munshi
	--Date:		02/04/2003
	--Comments:	This sp can compare two tables with the same schema
	--		and determine which records are different
	--Usage:	The following example assumes you have created this sp in Pubs and have created a Titles2 table similar to Titles and changed some data
	--		exec sp_Compare 'Titles','Titles2','Notes,PubDate','Title_Id'

	--CompType	1 = Records that exist in A but not in B
	--		2 = Records that exist in B but not in A
	--		3 = Records that exist in both A and B but one or more columns compared have different value
	--		4 = Records that exist in both A and B but all columns compared have same value
	--		0 = Do all 4 comparisons

	set nocount on
	declare @SQL 	varchar(8000),
		@select1 varchar(4000),
		@from1	varchar(4000),
		@where1	varchar(4000),
		@select2 varchar(4000),
		@from2	varchar(4000),
		@where2	varchar(4000),
		@select3 varchar(4000),
		@from3	varchar(4000),
		@where3	varchar(4000),
		@select4 varchar(4000),
		@from4	varchar(4000),
		@where4	varchar(4000),
		@ColName varchar(50),
		@field	varchar(50),
		@len	int,
		@start	int


	--Parse Exclude Fields and populate a temp table
	create	table #ExcludeFields(field varchar(50))
	select 	@start = 1
	select 	@len = Charindex(',',@ExcludeFields,@start)-1
	while	@len<>	-1
		begin
		select @field = substring(@ExcludeFields,@start,@len)
		insert into #ExcludeFields(field) values ('['+@field+']')
		select @ExcludeFields = substring(@ExcludeFields,@len + 2,len(@ExcludeFields))
		select 	@len = Charindex(',',@ExcludeFields,@start)-1
		end
	insert 	into #ExcludeFields(field) values ('['+@Excludefields+']')

	--Parse join fields and populate a temp table
	create	table #JoinFields(field varchar(50))
	select 	@start = 1
	select 	@len = Charindex(',',@JoinFields,@start)-1
	while	@len<>	-1
		begin
		select @field = substring(@JoinFields,@start,@len)
		insert into #JoinFields(field) values ('['+@field+']')
		select @JoinFields = substring(@JoinFields,@len + 2,len(@JoinFields))
		select 	@len = Charindex(',',@JoinFields,@start)-1
		end
	insert 	into #JoinFields(field) values ('['+@JoinFields+']')

	--Get a list of columns name in a temp table
	select	ColName = '[' + rtrim(sc.Name) + ']'
	into	#Columns
	from	syscolumns sc inner join sysobjects so on sc.id = so.id
	where	so.Name = @TableA
	order by colid

	--Initialize select from and where clause
	set	@select1 = 'select '
	set	@select2 = 'select '
	set	@select3 = 'select '
	set	@select4 = 'select '

	set	@from1 = 'from ' + @TableA + ' A left outer join ' + @TableB + ' B on '
	set	@from2 = 'from ' + @TableA + ' A right outer join ' + @TableB + ' B on '
	set	@from3 = 'from ' + @TableA + ' A inner join ' + @TableB + ' B on '
	set	@from4 = 'from ' + @TableA + ' A inner join ' + @TableB + ' B on '

	set	@where1 = 'where '
	set	@where2 = 'where '
	set	@where3 = 'where '
	set	@where4 = 'where '

	--Build select from and where clauses
	declare cBuildSQL cursor for
		select	ColName
		from	#Columns

	open	cBuildSQL
	fetch 	next from cBuildSQL into @ColName
	while 	@@fetch_status = 0
		begin
		--select clauses
		set 	@ColName = left(@ColName,len(@ColName)-1)
		--select 1
		select	@select1 = @select1 + @ColName + '_A] = A.' + @ColName + '], '

		--select 2
		select	@select2 = @select2 + @ColName + '_B] = B.' + @ColName + '], '

		--select 3
		select	@select3 = @select3 + @ColName + '_A] = A.' + @ColName + '], '
		select	@select3 = @select3 + @ColName + '_B] = B.' + @ColName + '], '

		--select 4
		select	@select4 = @select4 + @ColName + '_A] = A.' + @ColName + '], '
		select	@select4 = @select4 + @ColName + '_B] = B.' + @ColName + '], '

		set	@ColName = @ColName + ']'
		--from clause - Only include the fields in the Join list
		if 	@ColName in(select field from #JoinFields)
			begin
			select	@from1 = @from1 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
			select	@from2 = @from2 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
			select	@from3 = @from3 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
			select	@from4 = @from4 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
			end

		--where clause - Do not include the fields in the exclusion list
		if 	@ColName not in(select field from #ExcludeFields)
			begin
			if 	@ColName in(select field from #JoinFields)
				begin
				select	@where1 = @where1 + 'B.' + @ColName + ' is null and '
				select	@where2 = @where2 + 'A.' + @ColName + ' is null and '
				end
			else
				begin
				select	@where3	= @where3 + 'A.' + @ColName + ' <> ' + 'B.' + @ColName + ' or '
				select	@where4	= @where4 + 'A.' + @ColName + ' = ' + 'B.' + @ColName + ' and '
				end
			end

		fetch 	next from cBuildSQL into @ColName

		end
	deallocate cBuildSQL

	--get rid of trailing ',' from select clause, 'and' from from clause and 'or' from where clause
	set	@select1 = left(@select1,len(@select1)-1)
	set	@select2 = left(@select2,len(@select2)-1)
	set	@select3 = left(@select3,len(@select3)-1)
	set	@select4 = left(@select4,len(@select4)-1)

	set	@from1 = left(@from1,len(@from1)-4)
	set	@from2 = left(@from2,len(@from2)-4)
	set	@from3 = left(@from3,len(@from3)-4)
	set	@from4 = left(@from4,len(@from4)-4)

	set	@where1 = left(@where1,len(@where1) - 4)
	set	@where2 = left(@where2,len(@where2) - 4)
	set	@where3 = left(@where3,len(@where3) - 3)
	set	@where4 = left(@where4,len(@where4) - 4)

	if 	@debug = 1
		begin
		select 	field from #ExcludeFields
		select  field from #JoinFields

		select	select1 = @select1
		select	select2 = @select2
		select	select3 = @select3
		select	select4 = @select4

		select 	from1 = @from1
		select	from2 = @from2
		select	from3 = @from3
		select	from4 = @from4

		select 	where1 = @where1
		select 	where2 = @where2
		select 	where3 = @where3
		select 	where4 = @where4
		end

	if 	@CompType = 0
		begin
		--select	Message = 'The following are the records that exist in table A but not in table B'
		exec	(@select1 + ' ' + @from1 + ' ' + @where1)

		--select	Message = 'The following are the records that exist in table B but not in table A'
		exec	(@select2 + ' ' + @from2 + ' ' + @where2)

		--select	Message = 'The following are the records that exist in both table A and table B but one or more columns compared are different'
		exec	(@select3 + ' ' + @from3 + ' ' + @where3)

		--select	Message = 'The following are the records that exist in both table A and table B and all the columns compared are same'
		exec	(@select4 + ' ' + @from4 + ' ' + @where4)
		end

	if 	@CompType = 1
		begin
		--select	Message = 'The following are the records that exist in table A but not in table B'
		exec	(@select1 + ' ' + @from1 + ' ' + @where1)
		end

	if 	@CompType = 2
		begin
		--select	Message = 'The following are the records that exist in table B but not in table A'
		exec	(@select2 + ' ' + @from2 + ' ' + @where2)
		end


	if 	@CompType = 3
		begin
		--select	Message = 'The following are the records that exist in both table A and table B but one or more columns compared are different'
		exec	(@select3 + ' ' + @from3 + ' ' + @where3)
		end


	if 	@CompType = 4
		begin
		--select	Message = 'The following are the records that exist in both table A and table B and all the columns compared are same'
		exec	(@select4 + ' ' + @from4 + ' ' + @where4)
		end

	drop table #columns,#ExcludeFields,#JoinFields
end
GO

 

[fredericofonseca]

That's a pretty complex bit of code, and I am not going to do it all.

You will need 1 or more new columns on your tables, and the following is an example of what you would need to do.

select    @where3    = @where3 + 'A.' + @ColName + ' <> ' + 'B.' + @ColName + ' or '
select    @where4    = @where4 + 'A.' + @ColName + ' = ' + 'B.' + @ColName + ' and '

select    @where3    = @where3 + 'isnull(A.' + @ColName + ',' + @colname_null_value_replacement
                               + ') <> ' + 'isnull(B.' + @ColName + ',' + @colname_null_value_replacement + ') + ' or '
select    @where4    = @where4 + 'A.' + @ColName + ' = ' + 'B.' + @ColName + ' and '

Note that @colname_null_value_replacement is required as you need to distinguish between char and numeric fields

Similar changes will be required elsewhere on the code

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Catadmin]

Just a thought, have you tried to use the ISNULL() function to do a query-time replace of NULL values for your comparison?

For example: If I were checking an integar value that might have nulls, I'd use something like

Select ISNULL(Column2, 0)

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[magmo]

Hi Catadmin


Good idea, I'll go with that. Thanks!


Regards

 

[Catadmin]

Anytime. @=) Let us know how that works for you.





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[fredericofonseca]

If you look at my answer it is exactly what I said you had to do.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[magmo]

Hi Frederico


Yes I know. What I did is to use the Isnull() function in a stored procedure that I use earlier to fill the database table I later compare with another table. I then use the procedurer I posted above. So I have now prepared the data that I later compare so it will work.


Thanks for your help