Prevent Insert of strings containing only spaces?

[jpicks]

using SQL Server 2000, SP3.

I have a scheduled DTS package that queries against a source table and inserts the values into another table. The source table contains several char columns that contain only spaces. I am inserting these values into varchar columns and want to prevent inserting values that contain only spaces. Does anyone have any ideas on how to go about this? I was thinking of building a UDF to handle this, but need some suggestions to get me headed in the right direction.

Thanks in advance,

Jim

 

[mwolf00]

Why not just check the length of the trimmed field first?

IF LEN(lTrim(myField)) > 0 ....

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[jpicks]

Thanks mwolf00!

I wrote a function to handle this. I want other developers to be able to reuse the logic without having to write the if/then statements for the hundreds of fields I need to apply this to.

CREATE FUNCTION convertEmptyStringToNull(@FieldName varchar(100))
RETURNS varchar(100)
AS
BEGIN
   DECLARE @convertEmptyStringToNull varchar(100)
	IF LEN(LTRIM(@FieldName)) > 0
		Set @convertEmptyStringToNull = @FieldName
	ELSE
		Set @convertEmptyStringToNull = NULL
	RETURN(@convertEmptyStringToNull)
END