Welcome to WebmasterWorld Guest from 54.167.111.180

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)

New User

10+ Year Member

joined:Jan 14, 2005
posts:21
votes: 0


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)

Senior Member

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

joined:Sept 1, 2001
posts:4392
votes: 0


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)

New User

10+ Year Member

joined:Jan 14, 2005
posts:21
votes: 0


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)

Senior Member

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

joined:Sept 1, 2001
posts:4392
votes: 0


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

New User

10+ Year Member

joined:Jan 14, 2005
posts:21
votes: 0


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

damn it ;)

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

Administrator

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

joined:July 31, 2003
posts:12537
votes: 0


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members