| Welcome to WebmasterWorld Guest from 126.96.36.199 |
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
|Pubcon Platinum Sponsor 2014|
|Updating DB - insert a ´-´ in a series of numbers|
As topic says, I need to update a MySQL DB. I have a field with VARCHAR(10) and then any combination of 10 numbers, ie. 3243546576
I want to update all entries so that they look like :
VARCHAR(11) and 324354-6576
That´s an extra ´-´ before the last 4 numbers. How do I do this in a safe way? Tought about using a PHP script to do it for me somehow...?
hi, first of course you need to change your field length to 11 before you can do anything.
Then, the query is very easy:
update TABLE set field = left(field,6) & '-' & right(field,4)
that should do it.
UPDATE "TABLENAME" SET "FIELDNAME" = left("FIELDNAME",6) & '-' & right("FIELDNAME",4) where SOMETHING = '1'
It updated one entry where SOMETHING = 1, so that was np. But it didnt do what I wanted, it just set "FIELDNAME" to "0".
Good job that I didnt do an update all or 28000 entries would been set to 0 :D
What could be wrong?
Maybe it's a + instead of the &
nope tried a + instead of & .. same thing, set the field to 0
damn it ;)
The standard concatenation operator is usually the double pipe:
... LEFT(fieldname, 6) ¦¦ '-' ¦¦ RIGHT(fieldname, 4) ...
Note that the forum here breaks the pipe so you can't copy and paste, you'll have to rekey the pipe symbols.
All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved