Problem with converting to float

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with converting to float

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2009-09-02 : 07:34:43

I have stored procedure that converts and inserts rows into a another table. I'm looking for a solution that handles all illegal characters when converting, such as "line feed", blanks, .(punctuation mark) or , (comma). In other word all characters that makes the convert to float fail.

Any tips would help.

create table #BA_FAKTA(
FormularID int not null
,nr int not null
,col1 varchar(20) not null
,col2 varchar(20) not null
)

insert into #BA_FAKTA(id, nr, col1, col2) values (1, 1, '1 ')
insert into #BA_FAKTA(id, nr, col1, col2) values (2, 20, ' 7,80 ')
insert into #BA_FAKTA(id, nr, col1, col2) values (3, 30, '7.90 ')

create table #SS_FAKTA(
id int not null
,nr int not null
,col1 float
,col2 float
)

insert into SS_FAKTA
(
id
,nr
,col1
,col2
)
(
SELECT
id
,nr
,substring(replace(col1, ' ', '') as col1
,convert(float,ROUND(convert(float,replace(replace(col2, char(160), ''), ',','.')), 0)) as col2
FROM #BA_FAKTA
)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 08:27:28

If the data is 34K9,0.5 what do you want to have?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 08:28:57

use this function to get only numbers from string and then convert to float

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

maevr
Posting Yak Master

169 Posts

Posted - 2009-09-02 : 08:32:23

I managed to solve it using a function:

create function dbo.RemoveSpecialChars (@str varchar(256))
returns varchar(256)
begin
if @str is null
return null
declare @str2 varchar(256)
set @str2 = ''
declare @l int
set @l = len(@str)
declare @p int
set @p = 1
while @p <= @l begin
declare @c int
set @c = ascii(substring(@str, @p, 1))
if @c = 44 or @c = 46 or @c between 48 and 57
set @str2 = @str2 + char(@c)
set @p = @p + 1
end
if len(@str2) = 0
return null
return @str2
end

select dbo.RemoveSpecialChars(replace('abc- 12,13 @ # +ABC', ',', '.' ))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources