Sort alphanumeric value based on condition

  • Can anyone help me to solve the below issue?

    Issue:

    I need to sort alphanumeric value based on the condition.

    Sample Table:

    TableA

    ID

    1

    2

    2A

    200

    1000

    11

    90

    20

    2011

    My table name is TableA and field ID have above values. When user fires the condition like "Get values less than 20" then I need to pull the data from TableA from the field ID as below

    ID

    -----

    1

    2

    2A

    11

    20

    I tried using PATINDEX still no luck.

    LEFT(ID,PATINDEX('%[0-9]%',ID)-1)

    Please help me

  • You're on the right track. This should work:

    CREATE TABLE #John (ID varchar(4)); -- if your column is char instead of varchar, this won't work

    -- Constraint to check numeric characters come before alpha

    ALTER TABLE #John ADD CONSTRAINT CK_John_ID CHECK (ID NOT LIKE '%[A-Z][0-9]%');

    INSERT INTO #John VALUES ('1'),('2'),('2A'),('200'),('1000'),('11'),('90'),('20'),('20AB');

    SELECT ID

    FROM #John

    ORDER BY

    CASE

    WHEN ID NOT LIKE '%[A-Z]' THEN RIGHT('0000'+ID,5)

    ELSE RIGHT('0000'+ID,6+LEN(ID)-PATINDEX('%[A-Z]%',ID))

    END

    John

  • Hi John,

    Thanks a lot. How I can filter this with the condition? < 20.

  • SELECT ID

    FROM #John

    WHERE

    CASE

    WHEN ID NOT LIKE '%[A-Z]' THEN RIGHT('0000'+ID,5)

    ELSE RIGHT('0000'+ID,6+LEN(ID)-PATINDEX('%[A-Z]%',ID))

    END < '00020'

    If you're going to use it like this, you need to make sure you understand exactly how it works, since you'll be responsible for maintaining it and answering awkward questions about it.

    John

  • Following data normalization rules actually helps.

    If numeric and alphabetic parts of the values are meant to be processed separately then they must be stored in separate columns.

    If you cannot make this world better by changing the table design than you can normalize the data on fly:

    SELECT ID, CONVERT(INT,

    NULLIF(

    SUBSTRING(ID, 1, ISNULL(Split, LEN(ID)+1)-1)

    , '')

    ) AS NumID,

    ISNULL(

    SUBSTRING (ID, Split, LEN(ID)-Split+1)

    , '') CharID

    FROM (

    SELECT ID, NULLIF(PATINDEX('%[A-Z]%',ID), 0) Split

    FROM #John j

    ) DT

    This gives you the numeric part of the ID as integer, and the alphabetic part as string.

    Further manipulations must be easy.

    _____________
    Code for TallyGenerator

  • Just to throw another method in the ring to support Sergiy's idea of normalizing the data on the fly...

    These use the trick of if you specify a length for the 3rd parameter of a string that's greater the length of the string, it'll return up to the end of the string without the SUBSTRING building in any extra characters. Also threw in an extra ISNULL(xxx,8001) to help simplify the code a bit.

    --Sort numeric alpha strings

    --===== If the test table exists, drop it to make reruns easier.

    -- (This is not a part of the solution)

    IF OBJECT_ID('tempdb..#TableA','U') IS NOT NULL

    DROP TABLE #TableA

    ;

    --===== Create and populate the test table on the fly.

    -- (This is not a part of the solution)

    SELECT d.ID

    INTO #TableA

    FROM (

    SELECT '1' UNION ALL

    SELECT '2' UNION ALL

    SELECT '2A' UNION ALL

    SELECT '200' UNION ALL

    SELECT '1000' UNION ALL

    SELECT '11' UNION ALL

    SELECT '90' UNION ALL

    SELECT '20' UNION ALL

    SELECT '2011' UNION ALL

    SELECT '20AB' UNION ALL

    SELECT '2b' UNION ALL

    SELECT '2B' UNION ALL

    SELECT '2AB' UNION ALL

    SELECT '2a'

    ) d (ID)

    ;

    -------------------------------------------------------------------------------

    --===== Separate and sort the ID column

    -- This method only works with 2005 and up.

    WITH cteSplit AS

    ( --=== Determine where to split

    SELECT ID

    ,SplitPos = ISNULL(NULLIF(PATINDEX('%[A-Za-z]%',ID),0),8001)

    FROM #TableA src

    ) --=== Do the split

    SELECT ID

    ,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)

    ,ChrPart = SUBSTRING(ID,SplitPos,8000)

    FROM cteSplit

    ORDER BY NumPart, ChrPart

    ;

    -------------------------------------------------------------------------------

    --===== Separate and sort the ID column

    -- Taking a hint from Sergiy, this method works with all versions.

    SELECT ID

    ,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)

    ,ChrPart = SUBSTRING(ID,SplitPos,8000)

    FROM ( --=== Derived table to determine where to split

    SELECT ID

    ,SplitPos = ISNULL(NULLIF(PATINDEX('%[A-Za-z]%',ID),0),8001)

    FROM #TableA src

    ) d

    ORDER BY NumPart, ChrPart

    ;

    --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)
    Intro to Tally Tables and Functions

  • Jeff Moden (4/4/2016)


    ,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)

    It will return the same zero value for "0A" and "A".

    I do not like when 2 different source values turned into the same output value.

    It may cause such ID's switching places in output recordset from run to run.

    To avoid that I put NULLIF(...., '') in here.

    it will turn "0A" into 0--"A", and "A" into NULL--"A" defining a certain ordering sequence.

    _____________
    Code for TallyGenerator

  • Sergiy (4/4/2016)


    Jeff Moden (4/4/2016)


    ,NumPart = CAST(SUBSTRING(ID,1,SplitPos-1) AS INT)

    It will return the same zero value for "0A" and "A".

    I do not like when 2 different source values turned into the same output value.

    It may cause such ID's switching places in output recordset from run to run.

    To avoid that I put NULLIF(...., '') in here.

    it will turn "0A" into 0--"A", and "A" into NULL--"A" defining a certain ordering sequence.

    Ah... I see what you mean. I made the bad assumption that the numeric part would always be present. Thanks, Sergiy.

    --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)
    Intro to Tally Tables and Functions

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

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