Blank Spaces in Column Names with MySQL

Blank spaces in column names with MySQL

Use backticks.

SELECT * FROM `Area One`

How to select a column name with a space in MySQL

Generally the first step is to not do that in the first place, but if this is already done, then you need to resort to properly quoting your column names:

SELECT `Business Name` FROM annoying_table

Usually these sorts of things are created by people who have used something like Microsoft Access and always use a GUI to do their thing.

Rename all columns in a table removing whitespace

The following query will remove all whitespace from column names containing any white space in the table your_table in the database your_database. You can replace with the values you need.

SELECT
    CONCAT(
           'ALTER TABLE ', C.TABLE_NAME, ' CHANGE `', 
           C.COLUMN_NAME, '` `', REPLACE(C.COLUMN_NAME, ' ', ''), '` ',
           C.DATA_TYPE, ';'
          )
FROM
    INFORMATION_SCHEMA.COLUMNS C
WHERE
    TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table'
    AND C.COLUMN_NAME LIKE '% %';

Pay very close attention to the backticks which surround the column name (including the new column name). This will output a set of ALTER TABLE statements which look like the following:

ALTER TABLE your_table CHANGE `Old Column Name` `OldColumnName` VARCHAR;

MySQL add column with spaces in php

Do this:

mysqli_query($db, "ALTER TABLE msrk_krit ADD `test 1` VARCHAR( 255 )")

Notice that the single quotes around test 1 are actually back ticks, not quote marks.

Honestly though, you should avoid using spaces in your column names, it will be easier to maintain in the long run.

(Documentation: mysqli_query)

How do you deal with blank spaces in column names in SQL Server?

select [Response Status Code], [Client Response Status Code]
from TC_Sessions (NOLOCK) 
WHERE StartDate BETWEEN '05-15-2012' AND '06-01-2012' 
AND SupplyID = 3367 

Wrap the names in square brackets.

It is , however, best to avoid spaces in names if possible. It just creates more work for you down the road...

How to insert value into table column which is having spaces php and mysql

You need to use backticks to encapsulate the field names

INSERT INTO data(`First Name`) VALUES('Sample');

How to create a column name with space in SQL Server

Use brackets in SQL-Server

create table space_check
(
  [roll num] int,
  name varchar(50)
)

Why does my query break when I have spaces in my column names?

If you're going to have spaces in your column names, which is not a good idea, you need to wrap them in ticks:

mysql_query("create table account(
   `Fist Name` int(10) NOT NULL, 
   `Middle Name` varchar(10) NOT NULL, 
   `Last Name` varchar(10) NOT NULL,
   `Email` varchar(55) NOT NULL,
   `Comfirm Email` varchar(55) NOT NULL,
   `D.O.B` varchar(10) NOT NULL,
   `ID` int(11) NULL,  
   PRIMARY KEY (ID)
)") or die (mysql_error());

return the column that contains only value without white spaces

try to use the NOT LIKE syntax ; change your query to the following:

select name,id from details where name is not null and name not like '% %'

you can check this answer.

---

Related Topics