homepage Welcome to WebmasterWorld Guest from 54.211.47.170
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:1579665
 8:49 am on Oct 7, 2005 (gmt 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...?

 

txbakers




msg:1579666
 1:46 pm on Oct 7, 2005 (gmt 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.

Twixly




msg:1579667
 8:44 am on Oct 10, 2005 (gmt 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?

txbakers




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

Maybe it's a + instead of the &

Twixly




msg:1579669
 11:54 am on Oct 25, 2005 (gmt 0)

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

damn it ;)

coopster




msg:1579670
 3:26 pm on Oct 25, 2005 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved