Adding a space in between the data values

[razjafry]

Hi,
I want to add a space between postal code in my database but don't know how to accomplish it.
We have postal code format N1H8L5 or N1H 8L5 (note: a space between the middle of six characters. We have both kind of formats entered in our database.
I want to change them all to the the one format with the sapce in between.
How I can change those without space in between to the desired format.
Thanks for your help.
Syed

[ronverdonk]

Best I can come up now is, assuming pc is the table column to be changed[code=mysql]update table_name set pc=if(substring (pc,4,1)=' ',pc, concat(substr(p c,1,3),space(1) ,substring(pc,4 ,3)));[/code]You can expand it by also cheking the length of the 'pc' field.

Ronald

[razjafry]

Hi Ronald,
You are a great help. Thanks a lot. It did work for me and now I have all the postal codes with a space in between.
Thanks again for your help.

Syed

[ronverdonk]

Glad I could help you out. See ya!

Ronald

[razjafry]

Hi Ronald,
I hope I am not asking too much but would you please elaborate that code a little bit for me so I can understan how it did work. Its just part of my learning process.
Thanks again.
Syed

[ronverdonk]

Hi Ronald,
I hope I am not asking too much but would you please elaborate that code a little bit for me so I can understan how it did work. Its just part of my learning process.
Thanks again.
Syed

[code=mysql]SET pc=IF(SUBSTRING (pc,4,1)=' ',pc, CONCAT(substr(p c,1,3),SPACE(1) ,SUBSTRING(pc,4 ,3))); [/code]
the IF has the following syntax: IF(condition, result, result)
This returns the second argument result if condition (the first argument) is met, and the third argument is not.

In this statement meaning:
Update column pc in table with the condition that, when position 4 in the pc string is a blank, the content of pc is returned.
When the char in position 4 of pc is not a blank, then return a string made up of the first 3 chars of pc plus a blank plus the last 3 chars from pc.

Got it?

Ronald

[razjafry]

Thanks again Ronald,
So nice of you taking time to explain that to me.

Take care,
Syed

[ronverdonk]

At your service. See you.

Ronald