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
LVL 8
wal_toorAsked:
Who is Participating?
 
theGhost_k8Database 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
 
theGhost_k8Database 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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
theGhost_k8Database 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
 
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
 
wal_toorAuthor Commented:
I am sorry for the confusion. I did mean 'change all the fields where length <= 6'.

greetz,
walter
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.

All Courses

From novice to tech pro — start learning today.