Home » questions » SQL Server function to convert string to numerical value help?

SQL Server function to convert string to numerical value help?

1
2006-08-01 11:01:10, Category: Programming & Design
I have a field that contains a mixture of numerical data and alpha charaters. There is no specific spot where the numerical data begins and end. I need to create a fuction within SQL that will parse the string and return only the numerical value. This is my code so far (which is not working): CREATE FUNCTION [dbo].[CVAL] (@a nvarchar(50)) returns float as begin declare @myvar float declare @myval float declare @mytimes float declare @mylen float declare @X as int set @X = 1 SET @myvar = 0 set @myval = 0 set @mytimes = 1 set @mylen = len(@a); while @X <= @mylen begin if isnumeric(substring(@a,@X,1)) = -1 begin set @myvar = convert(float, substring(@a,@X,1)) set @myval = @myvar * @mytimes set @mytimes = @mytimes * 10 end set @X = @X + 1 end return @myval end

Answers

  1. Pands

    On 2006-08-01 13:49:44


    Try this, CREATE FUNCTION [dbo].[CVAL] (@a nvarchar(50)) returns float as begin declare @mylen int declare @vreturn_char nvarchar2(50) declare @vreturn_float float declare @X as int set @X = 1 set @vreturn_char = ''; set @mylen = len(@a); while @X <= @mylen begin if isnumeric(substring(@a, @X,1)) = 1 begin set v_return_char = v_return_char + substring(@a,@X,1) end set @X = @X + 1 end if len(trim(@vreturn_char)) >= 1 begin @vreturn_float = convert(float, trim(@vreturn_char)) end return @vreturn_float end