Post reply

Replace non numeric characters in string

  • August 14, 2008 at 1:02 am

    #857847

    Here is an updated version of fnExtractPostCodeUK function.

    CREATE FUNCTION dbo.fnExtractPostCodeUK

    (

    @data VARCHAR(8000)

    )

    RETURNS VARCHAR(8)

    AS

    BEGIN

    RETURN COALESCE(

    -- AANN NAA

    SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @data + ' '), 0), 8),

    -- AANA NAA

    SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789][ABEHMNPRVWXY] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @data + ' '), 0), 8),

    -- ANN NAA

    SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @data + ' '), 0), 7),

    -- AAN NAA

    SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][ABCDEFGHKLMNOPQRSTUVWXY][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @data + ' '), 0), 7),

    -- ANA NAA

    SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789][ABCDEFGHJKSTUW] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @data + ' '), 0), 7),

    -- AN NAA

    SUBSTRING(@Data, NULLIF(PATINDEX('% [ABCDEFGHIJKLMNOPRSTUWYZ][0123456789] [0123456789][ABDEFGHJLNPQRSTUWXYZ][ABDEFGHJLNPQRSTUWXYZ] %', ' ' + @data + ' '), 0), 6),

    -- Special case GIR 0AA

    SUBSTRING(@Data, NULLIF(PATINDEX('% GIR 0AA %', ' ' + @data + ' '), 0), 7)

     )

    END

    N 56°04'39.16"
    E 12°55'05.25"

  • August 14, 2008 at 6:52 am

    #858006

    Peso (8/14/2008)

    Here is an updated version of fnExtractPostCodeUK function.

    Once again, many thanks

    Dave J

    http://glossopian.co.uk/
    "I don't know what I don't know."

  • October 23, 2008 at 9:13 am

    #888669

    Updated algorithm to get not only valid postcodes, but also current postcodes in use today.

    See http://weblogs.sqlteam.com/peterl/archive/2008/08/13/Validate-UK-postcode.aspx

    N 56°04'39.16"
    E 12°55'05.25"

  • March 26, 2009 at 10:48 am

    #966851

    Thanks for this. A slight tweek and it met my needs perfectly.

  • November 22, 2009 at 5:56 pm

    #1082767

    RRB-392302 (3/26/2009)

    Thanks for this. A slight tweek and it met my needs perfectly.

    Heh... two way street here... why did you need to make a tweek and please post the code so we can see what you did.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 23, 2010 at 9:27 am

    #1123702

    This seems the fastest. Here is an enhanced version:

    ALTER FUNCTION dbo.DigitsOnly (@string VARCHAR(8000))

    /* TEST:

    Select [1]=dbo.DigitsOnly('1'), [1234]=dbo.DigitsOnly('asdf1234'), [4312]=dbo.DigitsOnly('asdf4321')

    Select Blank=dbo.DigitsOnly(''), BlankForNull=dbo.DigitsOnly(NULL), BlankForA=dbo.DigitsOnly('A')

    */

    RETURNS VARCHAR(8000) AS

    BEGIN

    declare @pos smallint

    if isnull(@string, '') = '' return ''

    while isnumeric(@string+'e0') = 0 and @string <> ''

    begin

    set @pos = patindex('%[^0-9]%',@string)

    set @string = replace(@string,substring(@string,@pos,1),'')

    end

    return @string

    END

  • February 23, 2010 at 10:16 am

    #1123744

    SSC Journeyman's algorithm seems to be the fastest. Here's an enhanced version:

    ALTER FUNCTION dbo.DigitsOnly (@string VARCHAR(8000))

    /* TEST:

    Select [1]=dbo.DigitsOnly('1'), [1234]=dbo.DigitsOnly('asdf1234'), [4312]=dbo.DigitsOnly('asdf4321')

    Select Blank=dbo.DigitsOnly(''), BlankForNull=dbo.DigitsOnly(NULL), BlankForA=dbo.DigitsOnly('A')

    */

    RETURNS VARCHAR(8000) AS

    BEGIN

    declare @pos smallint

    if isnull(@string, '') = '' return ''

    while isnumeric(@string+'e0') = 0 and @string <> ''

    begin

    set @pos = patindex('%[^0-9]%',@string)

    set @string = replace(@string,substring(@string,@pos,1),'')

    end

    return @string

    END

Viewing 7 posts - 76 through 81 (of 81 total)

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