Post reply

String formatting - Postcode

  • November 8, 2004 at 6:34 am

    #60646

    Hello,

    I am using SQL 2000.

    I have a field which is currently populated with UK Postcodes without spaces. i.e

    LE34TY

    BD153TY

    I need to update another field with the postcodes but formated as follows:

    LE3 4TY

    BD15 3TY

    Essentialy I need to insert a space after 3 characters from the right.

    Does anyone have any Ideas how to do this, I have tried to use the substring, charindex etc, but without any joy.

    Thanks in Advance

  • November 8, 2004 at 6:47 am

    #529255

    Untested! What about:

    SELECT

     'BD153TY', LEFT('BD153TY',LEN('BD153TY')-3)+ ' '+RIGHT('BD153TY',3)

     , 'LE34TY', LEFT('LE34TY',LEN('LE34TY')-3)+ ' '+RIGHT('LE34TY',3)

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 8, 2004 at 6:58 am

    #529258

    SELECT STUFF(@postcode,LEN(@postcode)-2,0,' ')

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 8, 2004 at 7:05 am

    #529261

    Aah, great solution for the lazycoder

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 8, 2004 at 7:14 am

    #529264

    Why run around the block when you can nip through the alley

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 8, 2004 at 7:18 am

    #529266

    Hello

    Thanks for getting the answer to me, changed franks code slightly to include the column name and hey presto!!

    I will try the other suggestion, but thanks to you both.

    Many thanks

     

  • November 8, 2004 at 7:20 am

    #529267

    Glad to hear it worked!

    Why run around the block when you can nip through the alley

    ...because one sometimes can't see the forest from the trees (or how do you say?).

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply