Welcome to WebmasterWorld Guest from 54.145.144.101

Forum Moderators: open

Message Too Old, No Replies

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

   
8:49 am on Oct 7, 2005 (gmt 0)

10+ Year Member



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

1:46 pm on Oct 7, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

8:44 am on Oct 10, 2005 (gmt 0)

10+ Year Member



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?

10:43 am on Oct 10, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Maybe it's a + instead of the &
11:54 am on Oct 25, 2005 (gmt 0)

10+ Year Member



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

damn it ;)

3:26 pm on Oct 25, 2005 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.