Thishan
February 28, 2022, 11:31pm
1
Hello,
I imported a file called sanfranciscofilmlocations.csv and one of the column headings is "Release Year". I tried the following statement but it gives "Release Year" for all rows. Could you help me with fixing it?
SELECT "Release Year" FROM sanfranciscofilmlocations;
As far as I understood, column headings cannot include two or more words but has to be linked; eg. "Release_Year" instead of "Release Year". I also tried to change the .csv file in that way but couldn't save as a .csv.
I searched online to come up with a solution, but didn't find a method. Please note that I am new to SQL.
You can use spaces but only if you include the entire column name in " or [].
You can rename the column if you want to:
EXEC sys.sp_rename 'dbo.sanfranciscofilmlocations.Release Year', 'Release_Year', 'COLUMN'
Hi Scott,
Thanks for your reply. When I included the entire column name in " " as below, I still get the error as shown below the command.
SELECT "Release Year" FROM sanfranciscofilmlocations;
Release Year
Following commands give the error in the command line itself.
EXEC sys.sp_rename 'dbo.sanfranciscofilmlocations.Release Year', 'Release_Year', 'COLUMN';
first try this
SELECT * FROM sanfranciscofilmlocations;
Your QUOTED_IDENTIFIER setting is OFF (which is not good, not standard), therefore double quotes are taken the same as single quotes.
Use brackets instead:
SELECT [Release Year] FROM sanfrancisofilmlocations;
Hi Yosiasz,
The following worked.
SELECT * FROM sanfranciscofilmlocations;
But not this
SELECT "Release Year" FROM sanfranciscofilmlocations;
Hi Scott,
How to turn on the QUOTED_IDENTIFIER?
I tried several options including SET QUOTED_IDENTIFIER ON
and SET QUOTED_IDENTIFIER { ON | OFF }
Both together and separately. But it gave an error.
Like this:
SET QUOTED_IDENTIFIER ON;
that is not a valid query. all it will do is return all rows with
What are the columns in sanfranciscofilmlocations table?
Title Release Year Locations Fun Facts Production Company Distributor
Director Writer Actor 1 Actor 2 Actor 3
not really helpful as we can't tell which column ends where but try this anyways
select Title, [Release Year], Locations, [Fun Facts] from sanfranciscofilmlocations
I think I downloaded csv file from searching SanFranciscoFilmLocations.csv on google.
It's the first item appears on the top (it doesn't allow me to send the link). At the bottom of the website (San Fransisco government website), you can see the table.
select Title, [Release Year] , Locations, [Fun Facts] from sanfranciscofilmlocations;
select Title, 'Release Year' , Locations, 'Fun Facts' from sanfranciscofilmlocations;
If you can suggest a way to change "Release Year" to "Release_Year" in the table, I think it would work.
I think the problem is that the table was created with quoted identifiers turned off. The table would need to be rebuilt with that setting turned on.
You can modify the column names using the following:
EXEC sp_rename 'dbo.ErrorLog.ErrorTime', 'ErrorDateTime', 'COLUMN';
See here: sp_rename (Transact-SQL) - SQL Server | Microsoft Docs
I did that in my very post for this q.
do
SELECT "Release Year" FROM sanfranciscofilmlocations
and post an image of your SQL results here
oops meant to say
SELECT * FROM sanfranciscofilmlocations
Also this is a Microsoft SQL Server forum
