CODEWITHSUNDEEP
postgresqlpostgresql-9.3
MAK
MAK

Reputation: 7260

Comparing remove white spaces

I have the float value to remove the white space from it.

Here is the following is the example.

In SQL Server: In SQL Server I used the following script.

DECLARE @f1 FLOAT = 74.3658319091568;

SELECT REPLACE(@f1,CHAR(160),'');

--Output

74.3658

PostgreSQL: In postgresql I used the following script.

do
$$
DECLARE 
    v_f1 float = '74.3658319091568';
    v_f3 text;

BEGIN

    SELECT regexp_replace(v_f1::varchar, '\s+$', '') INTO v_f3;
    Raise info '%',v_f3;

END;
$$

--Output

INFO:  74.3658319091568

I didn't get the result as I get in the SQL Server.

Upvotes: 1

Views: 214

Answers (2)

Andomar
Andomar

Reputation: 238186

Your Postgres float is a synonym for real, which has 6 digits precision. In SQL Server a default float has 15 digits precision.

You could use a Postgres double precision which also has 15 digits precision. There is no SQL Server type that has 6 digits precision.

Note: you normally don't get any whitespace when converting a float to a text type. So replacing whitespace is useless (but also harmless.)

Note 2: in SQL Server, when using a fixed-length char, trailing spaces are removed. So REPLACE(@f1,CHAR(160),'') replaces an empty string with an empty string.

Upvotes: 1

Vivek S.
Vivek S.

Reputation: 21915

You can try this way 

do
$$
DECLARE 
    v_f1 float8 = '74.3658319091568';
    v_f3 text;

BEGIN

    SELECT to_char( v_f1, 'FM999999.0000') INTO v_f3;
    Raise info '%',v_f3;

END;
$$

FM(prefix) -fill mode (suppress leading zeroes and padding blanks)

Using data type formatting

Upvotes: 1

Related Questions