Remove a character on a certain column

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

Moderators: macek, egami, gesf

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

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
gesf
Moderator
Moderator
Posts: 1717
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden
Contact:

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
planketa
New php-forum User
New php-forum User
Posts: 152
Joined: Mon Mar 08, 2004 12:29 am
Location: Philippines

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
gesf
Moderator
Moderator
Posts: 1717
Joined: Sun Dec 29, 2002 5:03 am
Location: Portugal / Sweden
Contact:

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);


Return to “phpMyAdmin”

Who is online

Users browsing this forum: Yahoo [Bot] and 1 guest