Retrieve minimum value and maximum value from a column in sql server

Indrashish Saha 1 Reputation point
2021-12-08T07:07:34.587+00:00

I have a column which has values like 49+-3. I need to extract the minumum value as 49-3 = 46 and the maximum value as 49 + 3 = 52 in 2 separate columns. Can you please suggest how to achieve this ?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,417 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,524 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Tom Phillips 17,711 Reputation points
    2021-12-08T17:40:50.11+00:00

    TSQL does not have any real way to convert a string "49-3" to the value 46. To do that requires you to run the actual SQL command.

    Without knowing more about your data, it is not possible to give you a solution.

    Please provide DDL and sample data and version of SQL Server.

    0 comments No comments

  2. Tom Phillips 17,711 Reputation points
    2021-12-08T17:51:05.787+00:00

    This should get you started:

    DECLARE @tbl TABLE (ID INT IDENTITY(1,1), val VARCHAR(1000));
    
    INSERT INTO @tbl (val)
    VALUES
    ('49+-3'),
    ('123.2+-17.2')
    
    DECLARE @sqlcmd VARCHAR(MAX);
    
    SELECT @sqlcmd = STRING_AGG(CAST(
        'SELECT ' + CAST(ID AS VARCHAR(100)) + ' as [ID], ' + REPLACE(val,'+','') + ' as [Val] UNION ALL ' + 
        'SELECT ' + CAST(ID AS VARCHAR(100)) + ' as [ID], ' + REPLACE(val,'-','') + ' as [Val] '
        AS VARCHAR(MAX)),' UNION ALL ')
    FROM @tbl
    
    SET @sqlcmd = 'SELECT ID, MIN([Val]) as MinVal, MAX([Val]) as [MaxVal] FROM (' + @sqlcmd + ') a GROUP BY ID';
    
    EXEC (@sqlcmd);
    
    0 comments No comments

  3. Sreeju Nair 11,696 Reputation points
    2021-12-08T18:29:48.887+00:00

    You can use stored procedures or functions to achieve your custom logic. You can have some string replacement to convert + or - to two expressions, and evaluate them.

    See the following sample that execute an expression from string.

    /****** Object:  StoredProcedure [dbo].[MyFunc]    Script Date: 12/8/2021 9:19:42 PM ******/  
    SET ANSI_NULLS ON  
    GO  
    SET QUOTED_IDENTIFIER ON  
    GO  
    ALTER PROCEDURE [dbo].[MyFunc]  
        @expression nvarchar(max)  
    AS  
    DECLARE @query NVARCHAR(MAX)  
    set @query = 'select ' + @expression  
    EXEC sp_executesql @query  
    

    Now let me call the stored procedure as below.

    EXEC dbo.MyFunc '5+7'  
      
    EXEC dbo.MyFunc '5+7-4'  
      
    EXEC dbo.MyFunc '6 / 2 * 3'  
    

    The result will be 12, 8, 9 respectively. See the below screenshots.

    156042-image.png
    156043-image.png

    Note:- Make sure you are doing the neccessary validation to clearout the possiblity for sql injection.

    0 comments No comments

  4. Viorel 110.1K Reputation points
    2021-12-08T18:41:39.597+00:00

    Check this interpretation too:

    declare @MyTable table (MyColumn varchar(max))
    
    insert @MyTable values ( '49+-3' )
    
    select MyColumn, a - b as [min], a + b as [max]
    from @MyTable
    cross apply (values (charindex('+-', MyColumn))) t1(i)
    cross apply (values (cast(left(MyColumn, i - 1) as int), cast(substring(MyColumn, i + 2, len(MyColumn)) as int))) t2(a, b)
    
    0 comments No comments