MySQL command to empty a column?

Does anyone know how to write a command for MYSQL to empty a column?

For instance I have a table called dogs and a column called tail_length - how do I empty the column tail_length of all its information without deleting the column itself?

Thanks.

 

Use update statement without a where clause.

UPDATE dogs set tail_length =''

 

update table_name set column_name="";

Code (markup):

His was cut off.

 

Thanks that's great!

Is there a command to remove spaces from between words in a single column, or maybe swap spaces for a character like - or _?

I say spaces because that's what I want to remove, but I guess it's the same as swapping any character - only I don't know how to do it. I've been painstakingly removing unwanted spaces in a column and it just occurred to me that there must be an SQL query that will do the same thing all at once?

Or I guess if there was a way to export a single column I could just use a text editor then import the column back in, only I don't know how to export single columns either...

 

you can write a php program to do this

1. select the coloumn
2. cycle through each record in coloumn one by one
3. use replace function to replace space between chars by ''
4. update the record

Regards

Alex

 

How would that look in PHP?

I've nearly done it with PHPMyadmin by deleting the columns I don't want, then saving the table so I only have the column I do want, then dropping the table and importing it back in again.

Now I have emptied the column I want to change and I have edited the column file I made above in Notepad, so I'm nearly there... but I can't figure out how to import the column data I made.

I've tried this:

INSERT INTO `DOGS_CATEGORY` (DOG_URL) VALUES('Springer_Spaniel');

DOG_URL is the column I'm trying to change, but the above query messes up other columns too, so I must have done something wrong?

 

If you want to remove the spaces in all the values of a certain column in your table, you can use this query:

UPDATE table_name SET column_name = REPLACE(column_name, ' ', '');

Code (markup):

Now, you can use this same query to swap a character say dash (-) or underscore (_) to replace the spaces. It would look like this:

use dash instead of space:

UPDATE table_name SET column_name = REPLACE(column_name, ' ', '-');

Code (markup):

use underscore instead of space:

UPDATE table_name SET column_name = REPLACE(column_name, ' ', '_');

Code (markup):

Hope that helps.

 

Thanks, that's great.

 

I know you want to clear the contents but I highly recommend using a standard value.

NULL is really not valid for a database like that. I would do an update to like NA or N/A or 0.

There is valid logic behind this.

Let's use this simple example:

Table1 has 3 fields id,x,y

Table contains 3 records as such:

1, Jon, Test
2, You,
3, Him, Test

select count(id) from table1;

Code (markup):

Will yield 3 records.

select count(y) from table1;

Code (markup):

Will yield 2 records.



It complete ignores the fact that the other columns contain data because you cannot count NULL.

This same rule will apply when using > <.

It's your database and you do as you feel but my suggestion is not to carry null values in the database.

 

I'm not sure what you mean, there are no null figures?

 

The previous response before mine stated

update something set field='';

Code (markup):

That would create a null value.

I was only giving you my opinion. You do not have to ad hear to anything I say.

I have just been doing this a while and things like that can get you later on down the road.

 

I'm not saying you're wrong, just that I don't understand what you mean. The column still has stuff in is, I just didn't want any spaces between the words.

 

AHHHHH. Ok SO you really want to do a str_replace and not so much an update.

You want to str_replace " " with ""

I follow now.

The previous poster had it update to ""

Which means it wipes the field data.

 

This is what I actually used in the end I think:

SELECT `tail_length`. replace(tail_length, ' ', '') from DOGS

 

give me your DB and I will do it for you.

 

Do you have difficulty with reading?