How to remove spaces in a string field and insert into float datatype?

Question

I have a varchar field with some data and I need to cast those values and insert into float data type field

create table #tableTest
(
    someData varchar(100) not null
);
insert into #tableTest(someData) values('5 215 243');


create table #tableFloat
(
    floatData float not null
);

insert into #tableFloat
select someData from #tableTest;

I tried this :

insert into #tableFloat
select replace(someData, ' ', '') 
from #tableTest

I except the result to be table #tableFloat have values 5215243.00 by removing the spaces in between them

`REPLACE(string, old_string, new_string)` then https://stackoverflow.com/questions/17730582/mssql-convert-varchar-to-float Did you even try to find an answer? — DavidG, Jun 03 '19 at 12:23
I have data like this for more than 50+ rows, hardcoding the string values is tedious, is there any other way to achieve this ? — Mar1009, Jun 03 '19 at 12:24
Perhaps if you spend a couple of minutes looking at this, it might be obvious. — DavidG, Jun 03 '19 at 12:27

Sreenu131 · Accepted Answer · 2019-06-03T12:53:53.890

1

Try This

CREATE TABLE #tabletest 
  ( 
     somedata VARCHAR(100) NOT NULL 
  ); 

INSERT INTO #tabletest 
            (somedata) 
VALUES     ('5 215 243'); 

CREATE TABLE #tablefloat 
  ( 
     FLoatdata FLOAT
   ); 

INSERT INTO #tablefloat 
SELECT  LTRIM(RTRIM(REPLACE(somedata, ' ', ''))) AS someData --Replace space values and appy trim function to remove spaces either side
FROM   #tabletest; 

SELECT   CAST(CAST(CAST(FLoatdata AS INT) AS VARCHAR(20))+'.00' AS DECIMAL(20,2)) FloatToDecmialData -- display the value of float datatype by converting in to decimal
FROM   #tablefloat

Result

data
----
5215243.00

edited Jun 03 '19 at 12:53

answered Jun 03 '19 at 12:25

Sreenu131

2,476
1
7
18

Why even bother casting to int? – DavidG Jun 03 '19 at 12:25
@Mar1009 Chek it now – Sreenu131 Jun 03 '19 at 12:31
1

@Sreenu131 OK, this is better but what we have here is a code dump with no explanation. Also, personally I wouldn't have answered this question as the OP has shown no effort in the question. – DavidG Jun 03 '19 at 12:33
@DavidG Ok bro i vl follow your suggestion, i agree that OP hasn't shown effort,but he may be new to sql so answered his question.Any how i will keep your word – Sreenu131 Jun 03 '19 at 12:36
thanks for the answer, but my problem is with inserting into FLOAT data type..If the #tablefloat has decimal data type it gets inserted but it doesn't work with FLOAT data type. thats the challange i'm facing.. excuse me if my questions in unclear – Mar1009 Jun 03 '19 at 12:37
@Mar1009 Can't u change into DECIMAL Type ,Don't u have rights to access – Sreenu131 Jun 03 '19 at 12:39
unfortunately I can't..plus there are many places I have used this data type as float..changing this might cause another problem? – Mar1009 Jun 03 '19 at 12:41
@Mar1009 check the answer now – Sreenu131 Jun 03 '19 at 12:55
1

This is a good answer to the question. But code only answers are very rarely great. You need to explain **why** your code works so the OP can learn and understand the solution. – Sean Lange Jun 03 '19 at 13:43
I agree with @DavidG – Mova Jun 03 '19 at 19:20

score 1 · Answer 2 · answered Jun 03 '19 at 20:06

I know this has been answered but here's my simplified solution (only one CAST and no trimming required):

CREATE TABLE #tabletest  (somedata VARCHAR(100) NOT NULL); 
CREATE TABLE #tablefloat (FLoatdata FLOAT); 
INSERT INTO #tabletest   (somedata) VALUES ('  5 215 243  '); 

INSERT #tableFloat SELECT CAST(REPLACE(t.somedata,' ','') AS FLOAT) FROM #tabletest AS t;

How to remove spaces in a string field and insert into float datatype?

2 Answers2