Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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

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
ltpitt
Asked:
ltpitt
  • 4
  • 3
  • 2
1 Solution
 
awking00Commented:
update table
set field = '+39' + field
where substring(field,1,1) <> '+';
0
 
awking00Commented:
Sorry, didn't see MySQL -
update table
set field = concat('+39',field)
where substring(field,1,1) <> 0
0
 
ltpittAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Dan CraciunIT ConsultantCommented:
@awking00: you forgot the +

WHERE SUBSTRING(field,1,1) <> '+'
0
 
ltpittAuthor Commented:
I tried:

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

but it addedd +39 to empty fields
0
 
Dan CraciunIT ConsultantCommented:
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
 
ltpittAuthor Commented:
Hail to the sql king :)
0
 
Dan CraciunIT ConsultantCommented:
Glad I could help, but this is awking00's solution.

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

Thank you.
0
 
ltpittAuthor Commented:
Oh sorry I thought you were the same user...

I'll try to fix this...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now