• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

Add space to field value in mysql

Hellow all,

I have this database of 12000 members. Each record has a zipcode field with dutch zipcodes. some peaple inserted 1234XX and others 1234 XX. So some people inserted a combination of 4 digits and 2 letters and some people inserted 4 digits, a space and 2 letters.

Like this

--------- zip field ------------
1234XX
8376 JH
3498 GF
8790DE
8754WE

Is there a mysql query that does something like this.

when lenght of field zipcode >= 7 insert space after first 4 characters of field zipcode

All entries should be like this.

--------- zip field ------------
1234 XX
8376 JH
3498 GF
8790 DE
8754 WE

Greetz,
walter
0
wal_toor
Asked:
wal_toor
  • 3
  • 3
1 Solution
 
K VDatabase ConsultantCommented:
Try this:
SELECT if(length(FIELD_NAME)>=7,INSERT(FIELD_NAME, 5, 1, ' '),FIELD_NAME) FROM TABLE_NAME;
0
 
wal_toorAuthor Commented:
this worked... now this is just a selection. How to change this..

SELECT if(length(postcode)<=6,INSERT(postcode, 5, 0, ' '),postcode) FROM spacetest;

(changed 1234XX in 1234 XX)
0
 
K VDatabase ConsultantCommented:
I couldn't understand this.. If you want to remove space from 4th place then,
SELECT if(length(postcode)<=6,replace(postcode,' ',''),postcode) FROM spacetest;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
wal_toorAuthor Commented:
hello,

I have tested:

SELECT if(length(postcode)<=6,INSERT(postcode, 5, 0, ' '),postcode) FROM spacetest;

And this inserts a space on the 5th position where the lengh is smaller or equel to 6. Just what i needed.

1234xx are 6 characters and 1234 xx are 7 characters.

So how to convert this query to a query that actually changes all the fields?

greet,
walter
0
 
K VDatabase ConsultantCommented:
Changes all the fields??!!
Your need is confusing...
If you want to update that field from 1234xx --> 1234 xx  then:
UPDATE spacetest set postcode=if(length(postcode)<=6,INSERT(postcode, 5, 0, ' '),postcode);
0
 
wal_toorAuthor Commented:
I am sorry for the confusion. I did mean 'change all the fields where length <= 6'.

greetz,
walter
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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