Microsoft SQL. SELECT FROM WHERE query with varchar type column

Question

Inside the SQL Server Management Studio I am trying a query like this:

In human words: Give me all rows from the table where the string in the column "name" is "alice".

My not working attempt on this SQL query:

SELECT * FROM MyTable WHERE ["name"]=alice

I am somehow lost on finding the right syntax for this. There is always errors when the column is a varchar type. The error is wiered: "...alice is not a valid column name.."

When the column is a real type it works. for example:

SELECT * FROM MyTable WHERE ["age"]=30

Hope someone can tell me what i am doing wrong here.

Thanks

Solution

The text conditions should be enclosed woth quotes. Try this query below.

SELECT * FROM MyTable WHERE name = 'alice' 

Comments

[Frank Thomas]

SELECT * FROM [DATABASENAME].[dbo].[MyTable] WHERE [Name] = 'alice' the brackets are used to disambiguate identifier names like feildnames, database names, schema names, etc from literal values. Whenever you use an (N)Varchar feild to compare it to a literal string, put the string in single quotes. Never put double quotes around an identifier. Be sure to fully qualify the table name, or use a use databaseName; GO statement at the top of the document, or select the active database from the drop down at the top of the query pane, so sql knows what database you mean.

[Frank Thomas]

Also note that you can drag tables and columns from Server Explorer in SMSS onto the query pane, and their fully qualified names will become part of your query. that way you can be sure everything is correctly identified and and spelled correctly. Never use double quotes in TSQL, and never put quotes around a fieldname or other identifier unless you are executing a string for a dynamic query (which is bad form in and of itself).

[tgg]

It gives me the same error. And when i use name without the quotes and brackets it errors on both (name and alice).

[tgg]

I do not understand that bracket syntax anyway because not one example i have seen on the web does use these brackets. But MS SQL wants them (gives errors otherwise). I am confused :)

[vembutech]

SELECT * FROM MyTable WHERE ["name"] = "alice"; can you try this then? normally sql doesn't need those square brackets

[tgg]

Yeah tried that too already and now again but it gives the same strange error. At least I feel bit less dumb now, seeing i was not totally wrong on how to do this. But anyway ms sql does not like it.

[tgg]

okay, there was no error at least, but: 1. I had to write ["name"] (with quotes) otherwise "not valid column error" 2. No error but it did not find anything (alice is there) :( 3. when typing "alice" there comes the "not valid column error" 4. when typing 'alice' nothing is found and 'alice' is highlighted red.

[tgg]

Sorry for the chaotic answer, hope it makes sense somehow. With "highlighted red" i mean in the query editor after typing it.

[Frank Thomas]

if intellisense (the red squigly line under name or alice) doesn't recognize the fieldname 'name' and you are sure you have spelled it correctly, that implies that SMSS doesn't know what Database the table MyTable exists in. MAke sure it is selected in the drop down list at the top, or fully qualify the table name, or use a use statement to identify the database.

[ᔕᖺᘎᕊ]

If you could explain the query, this would be a better answer.

[dominique.alons]

@ṧнʊß: My mistake on not explaining the query. The reason why I chose LIKE instead of = is, from my experience, allows for wildcard characters. As for the original question, what is the variable type assigned to the name column? Varchar, char, or something else?

[bwDraco]

Remember to properly format your question. Use a right angle bracket > before each line of a block quote, and indent code blocks with four spaces.