Post reply

Identify letter vs. number

  • December 19, 2007 at 7:44 am

    #761400

    The addition zero is coming from the fact that it's automatic code... it figures out that the "Page1" entry is 5 characters long and uses that asthe length for all the others.

    If you want to force 4 characters even though 5 or more may be present, then the following will work...

    DECLARE @TestData TABLE(page char(32))

    insert @testdata (page)

    select 'G18' union all

    select 'G2' union all

    select 'D99' union all

    select 'A08' union all

    select '16' union all

    select 'ENT' union all

    select 'M183' union all

    select 'PAGE1'

    --===== This is the solution for the known constraints on the data as posted

    DECLARE @MaxWidth TINYINT

    SET @MaxWidth = 4

    SELECT Original = page,

    Modified = LEFT(d.page,d.LastLetter)

    + RIGHT(

    REPLICATE('0',@MaxWidth)

    + SUBSTRING(d.page,d.LastLetter+1,@MaxWidth)

    , @MaxWidth-d.LastLetter)

    FROM (--==== Derived table "d" finds the interface between letters/digits

    SELECT Page = RTRIM(page),

    LastLetter = PATINDEX('%[a-z][0-9]%',RTRIM(page)+'0')

    FROM @TestData

    )d

    --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)

  • December 19, 2007 at 12:03 pm

    #761506

    Yep that does it. Thanks so much.

Viewing 2 posts - 16 through 16 (of 16 total)

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