Solved

Add +39 to beginning of mysql field only if field not beginning with +

Posted on 2014-04-02
9
283 Views
Last Modified: 2014-04-02
Hi all...

I have a lot of contacts in my database but some of them haven't international prefix.

I'd need an update query able to see if the number begins with "+" character.
If so, skip it, otherwise update its field preponing "+39".

Thanks for the help!
0
Comment
Question by:ltpitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39972677
update table
set field = '+39' + field
where substring(field,1,1) <> '+';
0
 
LVL 32

Expert Comment

by:awking00
ID: 39972690
Sorry, didn't see MySQL -
update table
set field = concat('+39',field)
where substring(field,1,1) <> 0
0
 
LVL 1

Author Comment

by:ltpitt
ID: 39972755
Excuse me if I forgot to mention this...

Will this query skip empty fields?

I'd like it to do so...

Just now I was thinking that if it doesn't find the + it could also mean that the field is empy.

Sorry for being a little slow :)
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39972757
@awking00: you forgot the +

WHERE SUBSTRING(field,1,1) <> '+'
0
 
LVL 1

Author Comment

by:ltpitt
ID: 39972810
I tried:

update contatti
set cellulare = concat('+39',cellulare)
WHERE SUBSTRING(cellulare,1,1) <> '+'

but it addedd +39 to empty fields
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39972822
Of course. We only tested if it has a + at the beginning.

To test for empty fields use
UPDATE contatti
SET cellulare = CONCAT('+39',cellulare)
WHERE SUBSTRING(cellulare,1,1) <> '+' AND cellulare <>''
0
 
LVL 1

Author Closing Comment

by:ltpitt
ID: 39972825
Hail to the sql king :)
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39972828
Glad I could help, but this is awking00's solution.

I just improved it slightly. Please re-grade accordingly.

Thank you.
0
 
LVL 1

Author Comment

by:ltpitt
ID: 39972884
Oh sorry I thought you were the same user...

I'll try to fix this...
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question