Board index   FAQ   Search  
Register  Login
Board index php forum :: Database phpMyAdmin

Remove a character on a certain column

How to use phpmyadmin.... phpmyadmin related arguments. Even phpMyAdmin issues

Moderators: macek, egami, gesf

Remove a character on a certain column

Postby planketa » Tue Oct 10, 2006 6:49 pm

Hi guys

How can I remove a character on a certain column. For example, I have a table in mysql and it has a column "cert_no" (varchar). This column should only have numbers in it, but for some reason when I imported a csv file, some values on the column has "-" before the number ex. -44567. How can i remove all the "-" before the number using phpmyadmin?

Thank you very much.
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

Postby gesf » Tue Oct 10, 2006 7:31 pm

Use the INT field type instead of VARCHAR.
Ex: cert_no INT(10)
Change this and make the import operation again.
User avatar
gesf
Moderator
Moderator
 
Posts: 1717
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden

thanks gesf

Postby planketa » Tue Oct 10, 2006 8:07 pm

hay man, thanks. but what if the records are already there. no other imports needed, is there a script in phpmyadmin that can do that?
User avatar
planketa
New php-forum User
New php-forum User
 
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

Postby gesf » Wed Oct 11, 2006 12:24 am

Never did such thing, but... try this out.
I can't try it right now, but got a great feeling :)
Code: Select all
UPDATE table SET field = SUBSTRING(field, - 1);
User avatar
gesf
Moderator
Moderator
 
Posts: 1717
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden


Return to phpMyAdmin

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron