CODEWITHSUNDEEP
t-sqlsql-server-2012
UserSN
UserSN

Reputation: 1013

T-SQL Insert null if ' ' input is empty

My web-application allows for token replacements and therefore my SQL INSERT query looks something like this:

INSERT INTO mytable (Col1, Col2, Col3)
VALUES ('[Col1Value]', '[Col2Value]', '[Col3Value]')

The web app is replacing whats inside the brackets [Col1Value] with the input entered into the form field.

Problem is when an input field is left empty this query is still inserting ' ' just an empty space so the field is not considered null

I'm trying to use SQL's default value/binding section so that all columns that are null have a default value of -- but having my query insert a blank space ' ' is making it so SQL does not trigger the default value action and it still shows blank rather than my desired default value of --

Any ideas as to how I can solve this and make sure ' ' is inserted as a null rather than a space or empty space so it will trigger SQL replacing null with my default value of --

Upvotes: 0

Views: 3791

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

There is no easy going...

How are you inserting the values? If you create these statements literally you are stumbling on the dangerous fields of SQL injection... Use parameters!

One approach might be an insert through a Stored Procedure, another approach is an Instead Of TRIGGER and the third uses the fact, that the string-length does not calculate trailing blanks:

SELECT LEN('')   --returns 0
      ,LEN('  ') --returns 0 too

You can use this in an expression like this:

CASE WHEN LEN(@YourInputValue)=0 THEN NULL ELSE @YourInputValue END

Upvotes: 1

Related Questions