| Author |
Topic |
|
robtop
Starting Member
9 Posts |
 Posted - 2011-10-18 : 07:01:53
|
| Good Morning,I've received a CSV file which has all the UK postcodes in it along with some other data.The problem I have is that the post code is in one long string without any spaces eg NN26BF, B12HB etc.Does anyone know the best way of splitting this string out in SQL to show in the normal format e.g NN2 6BF, B1 2HB?Thanks very muchRegardsRob Topliss |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-18 : 07:06:57
|
| I think it can be xxnn nxx, xn nxx, xxn nxx, xxnx nxx, xnx nxxSo it means isolating the last 3 charactersselect left(col, len(col)-3) + ' ' + right(col,3)But I'll bet there are other formats or errors in the data.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-18 : 07:47:46
|
| DECLARE @FullName VARCHAR(100)SET @FullName = 'NN26BF'SELECT SUBSTRING(@FullName,0,LEN(@FullName)-2) + ' ' + SUBSTRING(@FullName,LEN(@FullName)-2, LEN(@FullName))AS [postcodes] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-18 : 08:33:11
|
Do you need to even bother? most things I encounter seem to treat the postcode as a single data element, without spaces. Perhaps the post office don't care if there is no space when written on an envelop?Pity the GPO went with such a variable format code in the first place  "I think it can be xxnn nxx, xn nxx, xxn nxx, xxnx nxx, xnx nxx"Also:xnn nxxand:GIR 0AA although you might not need to cater for that one, as there's only one ...SAN TA1 Ditto for Father Christmasand probably some other that break the mould ... |
|
|
|
sqlmaster555112
Starting Member
13 Posts |
Posted - 2011-10-18 : 11:37:30
|
| Look you need to find a trend kind of a thing, pattern identification is a must in these cases.If say first 3 letters out of the code NN26BF are the postal code we can try ofSelect IIf(len([postal code])<6,left([Postal code],2),left([postal code],3)) as[Post Code] from...........The IIF function I have stated says that If the length of the postal code given is lesser than 6,then we take out first two letters as the postal code as you have written in the question else if it is six then we are taking out the first three letters as the postal code.Thanks try and revert.@sqlmasterhttp://www.intelcs.com/SQL_Server_Consultant.aspx |
|
|
|
Eddie M
Starting Member
14 Posts |
Posted - 2011-10-20 : 07:28:44
|
quote:
Originally posted by sqlmaster555112
Look you need to find a trend kind of a thing, pattern identification is a must in these cases.
You're making it much too complicated. Whatever the lenght of the postcode, or the arrangement of characters in the first half of it, the space always goes before the last three characters. So the solution given by Paul Tech is the one to use.For more information about the postcode format, see:http://www.ml-consult.co.uk/foxst-39.htmEd. |
|
|
|
Eddie M
Starting Member
14 Posts |
Posted - 2011-10-20 : 07:45:54
|
Or a slightly easier way:SELECT stuff(@postcode, len(@postcode)-2, 0, ' ') AS Postcode Eddie |
|
|
|
jassi.singh
Posting Yak Master
122 Posts |
Posted - 2011-10-20 : 08:09:00
|
| Hi,check following SELECT stuff('NN26BF', len('NN26BF')-2, 0, ' ') Just change the value which is subtracted to get the desired format.If you deduct 3 then it will leave one space before last 4 character, i.e. if you specify -1 then it will leave one space before last 2 character. You can place '-' instead of space by just replacing ' ' with '-'Please mark answer as accepted if it helped you.Thanks,Jassi Singh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
Splitting a Postcode String