Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Location
    Thane, Maharashtra,India
    Posts
    17

    Unanswered: Equivalent of val() function in SQL server?

    what is the equivalent of val() function in SQL server?
    and cstr() function

  2. #2
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Ummm .... is this what you are looking for?

    You don't mention what programming language you've compared the val() and cstr() functions from, but it sounds to me that you are trying to convert a value to string with the cstr() - function. If that is the case, look up CONVERT in the SQL Server books online. The val()-function I cannot help you with unless you describe what the function does.
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

  3. #3
    Join Date
    Jun 2010
    Posts
    1
    bit of a late reply but I was looking for the answer too, didn't find it so rolled my own. Basically VBA's val() returns the numeric part of a string so Val("123ABC") would return 123.

    create function Val
    (
    @text nvarchar(40)
    )
    returns float
    as begin
    -- emulate vba's val() function
    declare @result float
    declare @tmp varchar(40)

    set @tmp = @text
    while isnumeric(@tmp) = 0 and len(@tmp)>0 begin
    set @tmp=left(@tmp,len(@tmp)-1)
    end
    set @result = cast(@tmp as float)

    return @result
    end

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You got me thinking how best to do this. Just playing around, it can "error" (google problems with SQL Server IsNumeric). An inline table function would be more efficient too.
    Code:
    USE test
    GO
    
    IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE type_desc = 'SQL_INLINE_TABLE_VALUED_FUNCTION' AND object_id= OBJECT_ID('dbo.val')) 
    BEGIN
        EXEC('
    CREATE FUNCTION  dbo.val(@input  INT)
    RETURNS TABLE
    AS
    RETURN
        (
            SELECT  daCol   = NULL
         )')
    END
    GO
    
    ALTER FUNCTION dbo.val
    --WITH SCHEMABINDING
        (
              @input  NVARCHAR(20)
        )
    RETURNS TABLE
    AS
    RETURN
        (
            SELECT  daReturn   = CASE WHEN @input LIKE '[0-9.]%' THEN CAST(SUBSTRING(@input, 1, PATINDEX('%[^0-9.]%', @input) - 1) AS DECIMAL(38, 20)) END
        )
    GO
    
    IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE type_desc = 'SQL_SCALAR_FUNCTION' AND object_id= OBJECT_ID('dbo.Val2')) BEGIN
        EXEC('
    CREATE FUNCTION  dbo.Val2()
    RETURNS INT
    AS
    BEGIN
        RETURN    NULL
    END'
        )
    END
    GO
    
    alter function dbo.Val2
    (
    @text nvarchar(40)
    )
    returns float
    as begin
    -- emulate vba's val() function
    declare @result float
    declare @tmp varchar(40)
    
    set @tmp = @text
    while isnumeric(@tmp) = 0 and len(@tmp)>0 begin
    set @tmp=left(@tmp,len(@tmp)-1)
    end
    set @result = cast(@tmp as float)
    
    return @result
    END
    GO
    
    SELECT  dbo.Val2(daCols), val.daReturn
    FROM    
            (
                SELECT  daCols      = '123ABC'
                UNION ALL
                SELECT  '123$'
                UNION ALL
                SELECT  '123' + CHAR(164)
                UNION ALL
                SELECT  '1.7e-2'
            ) AS some_values
    CROSS APPLY
            dbo.val(daCols) AS val

  5. #5
    Join Date
    Aug 2011
    Posts
    1

    Thanks

    Thanks just what I needed! Looked for a long time before finding your answer. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •