DataCrypt DataCrypt - 12 days ago 5
SQL Question

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

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

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
Comments