CODEWITHSUNDEEP
sqlsql-servert-sqlinsert-update
DataCrypt
DataCrypt

Reputation: 307

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.

Upvotes: 0

Views: 9182

Answers (2)

Igor
Igor

Reputation: 62308

You could use IIF on all the values.

I am not sure which are the varchar values so I am going to take a guess. Also I have no idea how you are getting your values so I will assume hard coded for now.

INSERT INTO Vendors (LegacyID, ExternalID1, ExternalID2, EIN, LegalName, DBAName, ...) 
VALUES (LegacyID, ExternalID1, ExternalID2, EIN, IIF(LegalName = '', ' ', LegalName), IIF(DBAName = '', ' ', DBAName), ...)

-- or

Update Vendors SET LegacyID = ds.LegacyID
    , ExternalID1 = ds.ExtID1
    , ExternalID2 = ds.ExtID2
    , LegalName = IIF(ds.LegalName = '', ' ', ds.LegalName)
    , DBAName = IIF(ds.DBAName = '', ' ', ds.LegalName)
    ...

Also I hope you have a valid reason/business case for doing this. If you really want an empty value the best thing to use is an empty string and for no value use null. Persisting a space only seems like an odd requirement, probably something that is probably only needed in the presentation layer but should probably not make it down into the persistence store.

Upvotes: 1

Tyler Roper
Tyler Roper

Reputation: 21672

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

Upvotes: 1

Related Questions