0

I have a table with some VARCHAR(MAX) columns in it. If I open SSMS and right-click on the table and say “Edit Top 200 Rows”, I can select that cell within a row and enter many different arbitrary values. What I want to do is update that cell to the value 'NULL' which would be similar to running this SQL command:

UPDATE dbo.TableName SET ColumnName = '''NULL''' WHERE Id = 1;

However, when I write 'NULL' into the editor’s cell, the effect is that the string NULL is written into the table. I.e., the editor behaves as if this command was executed instead of putting in the text I want:

UPDATE dbo.TableName SET ColumnName = 'NULL' WHERE Id = 1;

How do I use the editor to set the value of the cell to 'NULL'? To clarify, I want the value of the cell to be a 6 character string. The characters in this string are, in order, single quote, capital N, capital U, capital L, capital L, and single quote. How do I achieve this using the “Edit Top 200 Rows” table editor’s Results Pane in SQL Server Management Studio?

The documentation doesn’t seem to mention this scenario. I have opened a docs bug.

Note that this question is not the same as How to enter 'NULL' into SSMS cell?

binki
  • 7,754
  • 5
  • 64
  • 110
  • @PatrickHonorez I don’t follow. That would set the cell to the empty string. I want to set it to the string which is first a single quote, then the capital letter N, then the capital letter U, then the capital letter L, then the capital letter L, and finally another single quote. – binki Feb 03 '20 at 21:40
  • 1
    I noticed that `'null'` (lower case) works, but indeed it's hard to find a solution for uppercase – Julian Feb 03 '20 at 22:01
  • 1
    I'm curious why you need this ;) Is this just poor design or is there really a good reason? – Julian Feb 03 '20 at 22:40
  • @Julian this behavior confused us because we were trying to get the value of a cell to be `'NULL'`. We assumed that the value we inserted was `'NULL'` since that is what we typed and what the editor displayed. We needed this value because it was given to us by a vendor as the value to match on. Later, when checking with manually authored SQL commands, we discovered that the four character string `NULL` was being inserted instead. So, we now know how it works and were just curious if there even **was** a way to insert that value with the simple editor. – binki Feb 03 '20 at 22:54

1 Answers1

1

Found a way! (Warning: need some steps!)

  1. Go to Edit

    enter image description here

  2. Change type to 'Insert Values'

    enter image description here

  3. Show criteria pane

    enter image description here

  4. Type '''NULL''' (so surrounded with triple quotes)

    enter image description here

  5. Execute (e.g. Ctrl + R)

proof:

enter image description here

I hope this is what you're looking for. It has some steps, but at least you don't need to write SQL.

Julian
  • 33,915
  • 22
  • 119
  • 174
  • I think this will be as close as I can hope for. I'll accept it for now ;-) – binki Feb 03 '20 at 22:36
  • This method is quite removed from editing the Results pane, which is apparently what I hoped to find a solution for. If you want to edit an existing value (which is what I wanted), you can’t use the “Insert Values” type but have to use the “Update” type. Be careful to specify a Filter if you do this! Honestly, I think your answer would be better if you could just say “No, there isn’t a way. But here’s the closest way of getting the GUI to help you…” – binki Feb 04 '20 at 01:20
  • Since I now know what the Results Pane is ;-) and I originally intended to ask how that could be done and this answer doesn’t use that, I will unaccept it as an answer for now (but if you update this to say “can’t be done with results pane”, it becomes acceptable to me ;-)—poke me if you do). – binki Feb 04 '20 at 01:23
  • I'm confused. The original pane for editing is not the results pane? Also I don't think there is another option (and otherwise feel free to add an answer). I'm confused it's now unaccepted. – Julian Feb 04 '20 at 14:17
  • The [Results Pane](https://learn.microsoft.com/en-us/sql/ssms/visual-db-tools/results-pane-visual-database-tools?view=sql-server-ver15) is distinct from the [Criteria Pane](https://learn.microsoft.com/en-us/sql/ssms/visual-db-tools/criteria-pane-visual-database-tools?view=sql-server-ver15). Yeah, I also don’t think there is another option. So I think the answer should simply be “it’s impossible” with the Results Pane. – binki Feb 04 '20 at 14:19