Post reply

TSQL - Minimum of numeric value

  • February 15, 2006 at 2:32 pm

    #110986

    SET NOCOUNT ON

    DECLARE @MyTable TABLE

    (

    MyCol VARCHAR(12)

    )

    INSERT @MyTable

    SELECT 'ABS0234651' UNION ALL

    SELECT 'DBS0004657' UNION ALL

    SELECT 'AFGBS004SA55' UNION ALL

    SELECT 'ABSFR3465' UNION ALL

    SELECT 'ABS003412' UNION ALL

    SELECT 'ASA123465' UNION ALL

    SELECT 'AAWE0011' UNION ALL

    SELECT 'SBS0010'

    /*

    Needed result is minimum of numeric value after removing the non numeric characters

    I Need a result 'SBS0010' 

    */

    Regards,
    gova

  • February 15, 2006 at 2:46 pm

    #621205

    Insufficient info, based on your sample data.

    eg: Data value  'AFGBS004SA55'

    Is that the number 4 ? Or 55 ? or 455 ? What are the requirements/rules for parsing a value with 2 or more blocks of numbers ?

     

  • February 15, 2006 at 2:49 pm

    #621207

    Remove all characters in data. You have only numeric value now. Get the minimum of that numeric value. Return corressponding value from the column.

    Data value  'AFGBS004SA55' is 00455

    Regards,
    gova

  • February 15, 2006 at 3:04 pm

    #621213

    You can write a function that goes through the string one character at a time, does a isnumeric() check on it - if it is 0, then replaces it with '' else uses that number...the purpose of the function will be to take a string and return back the string of number values only.  Then, you can do operations on that string - be aware though that number values treated as string will have different sort/comparison rules so once the string of the number values only has been formed, you may want to cast it as an int before doing a MIN().

    Hth

  • February 15, 2006 at 3:06 pm

    #621215

    CREATE FUNCTION fn_StripChars(@val varchar(50)) RETURNS int

    AS

    BEGIN

    DECLARE @int varchar(50)

    WHILE LEN(@val) > 0

    BEGIN

    IF ISNUMERIC(LEFT(@val,1)) = 1 SET @int = ISNULL(@int,'') + LEFT(@val,1)

    SET @val = RIGHT(@val,LEN(@val)-1)

    END

    RETURN (SELECT CAST(@int AS int) AS intValue)

    END

    SELECT MIN(dbo.fn_StripChars(field1))

    FROMtable1

  • February 15, 2006 at 3:43 pm

    #621220

    great minds think alike

  • February 15, 2006 at 3:48 pm

    #621221

    If you return int you not gonna have 00455, it will be just 455.

    If you really need 00455 it must be varchar.

    _____________
    Code for TallyGenerator

  • February 15, 2006 at 4:22 pm

    #621226

    Here is the query using the Function from Kevin...

    select mycol from

    (

    SELECT  top 1 mycol,MIN(dbo.fn_StripChars(MyCol)) minvalue

    FROM @MyTable

    group by mycol

    order by minvalue

    ) as a

    -Krishnan

  • February 15, 2006 at 6:57 pm

    #621247

    Thanks to Kevin and all answers. 455 is good for this time.

    This table has another columns. I got to group by and get the minimum number. If I loop through characters and strip them and after that getting answer takes forever testing users patience. Stripping VARCHAR(32) Col which has 100,000 rows is 32 * 100000 charactes each time.

    I would like some thing like set ascii characters in another table and use replace method. This wouldn't work. If a super brain can give workable solution that what I need.

    DECLARE @MyAscii TABLE (AsciNum INT)

    INSERT @MyAscii SELECT 65 UNION SELECT 66 .... SELECT 125

    SELECT ColA, MIN(Replace(MaiCol, CHAR(AsciiNum), '')) FROM MainTbl GroupBy ColA

    Regards,
    gova

  • February 16, 2006 at 12:04 am

    #621265

    Govinn,

    If you don't already have a "Tally" table, now's the time to make one... here's the code to make a Tally table...

    --===== Create and populate a Tally table on the fly

     SELECT TOP 9999

            IDENTITY(INT,1,1) AS N

       INTO dbo.Tally

       FROM Master.dbo.SysComments sc1,

            Master.dbo.SysComments sc2

    --===== Give it a primary key

      ALTER TABLE dbo.Tally

            ADD CONSTRAINT PK_Tally_N

                PRIMARY KEY CLUSTERED (N)

    --===== Give everyone "SELECT" rights

      GRANT SELECT ON dbo.Tally TO PUBLIC

    ...and that makes the solution to your problem both easy and high speed...

     CREATE FUNCTION dbo.StripChars( @String VARCHAR(32))

    RETURNS VARCHAR(32)

         AS

      BEGIN

            DECLARE @Result VARCHAR(32)

                SET @Result = ''

             SELECT @Result = @Result + SUBSTRING(@String,N,1)

               FROM dbo.Tally

              WHERE N<=LEN(@String)

                AND SUBSTRING(@String,N,1) LIKE '[0-9]'

     RETURN (NULLIF(@Result,''))

        END

    ... It will resolve 100,000 records in about 6 seconds on my humble 1.8 Ghz desktop machine.

    Here's the usage example...

    SELECT dbo.StripChars(somecolname)

      FROM dbo.sometablename

    ... and it preserves the leading zeros.  If you don't want them, don't change the function (it's useful for other things)... do this instead...

    SELECT ID,CAST(dbo.StripChars(somcolname) AS INT)

      FROM sometablename

    It doesn't add any apparent time to the run.

    --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 16, 2006 at 6:51 am

    #621345

    Thanks a million Jeff. That solved my problem.

    Regards,
    gova

Viewing 11 posts - 1 through 10 (of 10 total)

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