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
Solved

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

Posted on 2014-04-02
9
279 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 34

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 34

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 34

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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