SQL INSERT INTO statement - how to insert space when field is an empty string?

Question

I have an INSERT INTO statement that I need to check if one of the field values I'm inserting is an empty string or not. If it is, I need to insert an actual SPACE instead of an empty string.

INSERT INTO Vendors (LegacyID, ExternalID1, ExternalID2, EIN, LegalName, DBAName, ...)

How would I determine if EIN is an empty string here ('') and if so, actually insert a space (' ')?

The same for an UPDATE statement

Update Vendors SET LegacyID = ds.LegacyID, ExternalID1 = ds.ExtID1, ExternalID2 = ds.ExtID2, ...)

Any help and examples would be greatly appreciated. Thank you.

Answer 1

You didn't include the VALUES portion of the INSERT, nor the EIN in your UPDATE, so you've omitted the sections where you'd actually be making changes.

Because of this, I could only take a best-guess shot at what your actual fields may be, but the general jist of the answer is to use a CASE statement around the value going to be inserted.

Example:

-- If field is '', then use ' '.
-- Otherwise, use it as-is.
CASE field WHEN '' THEN ' ' ELSE field END

Potential use-case (field names assumed):

INSERT INTO Vendors (EIN, ds.LegacyID, ...) VALUES (CASE ds.EIN WHEN '' THEN ' ' ELSE ds.EIN END, ds.LegacyID, ...)

UPDATE Vendors SET EIN = CASE ds.EIN WHEN '' THEN ' ' ELSE ds.EIN END