1

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

3
  • 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. Nov 21, 2014 at 20:44
  • 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). Nov 21, 2014 at 20:54
  • 1
    There is also the possibility that your table belongs to a schema that is not your users default schema for the database. if that is the case, you must qualify the schema (dbo in my example above), but this is less likely so try it last. dragging the table from Object Explorer will fix that. Nov 21, 2014 at 21:23

3 Answers 3

1

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

SELECT * FROM MyTable WHERE name = 'alice' 
5
  • It gives me the same error. And when i use name without the quotes and brackets it errors on both (name and alice).
    – tgg
    Nov 21, 2014 at 19:26
  • 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 :)
    – tgg
    Nov 21, 2014 at 19:28
  • SELECT * FROM MyTable WHERE ["name"] = "alice"; can you try this then? normally sql doesn't need those square brackets
    – BDRSuite
    Nov 21, 2014 at 19:30
  • 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
    Nov 21, 2014 at 19:35
  • 1
    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. Nov 21, 2014 at 20:50
0

Try:

SELECT * FROM Table WHERE name LIKE 'alice'

If you're using SQL Server:

SELECT * FROM Table WHERE [name] LIKE 'alice'
5
  • 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
    Nov 21, 2014 at 19:43
  • Sorry for the chaotic answer, hope it makes sense somehow. With "highlighted red" i mean in the query editor after typing it.
    – tgg
    Nov 21, 2014 at 19:47
  • 1
    If you could explain the query, this would be a better answer. Nov 21, 2014 at 21:07
  • @ṧнʊß: 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? Nov 21, 2014 at 21:28
  • 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.
    – bwDraco
    Nov 21, 2014 at 21:39
0

columns are denoted by square brackets ([]). String literals are denoted by single quotes ('):

SELECT * FROM MyTable WHERE [name] = 'alice'
1
  • or double quotes (")
    – user373230
    Nov 29, 2014 at 14:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .