Welcome to WebmasterWorld Guest from 54.147.10.72

Forum Moderators: open

Message Too Old, No Replies

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

     

Twixly

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

txbakers

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.

Twixly

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?

txbakers

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 &

Twixly

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

coopster

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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month