Updating DB - insert a ´-´ in a series of numbers

[Twixly]

Hello!

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...?

[txbakers]

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.

[Twixly]

I tried..

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?

[txbakers]

Maybe it's a + instead of the &

[Twixly]

nope tried a + instead of & .. same thing, set the field to 0

damn it ;)

[coopster]

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.