Re: Type casting text to Numeric - Query Error

Hello

2011/8/17 Vikram A <vikkiatbipl(at)yahoo(dot)in>:
> Hi there,
> I have the following definitions,
> 1. CREATE TABLE Sampletemp ( Sampleid serial NOT NULL,  SampleText character
> varying(50), CONSTRAINT Sampletemp_id PRIMARY KEY (Sampleid));
> 2. Insert into Sampletemp (SampleText) values ('Mr. Raja'),('Mr.
> Alex'),('1000'),('2500'),('555');
> 3. select sum(SampleText) as SampleText from Sampletemp;
> ERROR:  function sum(character varying) does not exist
> LINE 3:   select sum(SampleText) as SampleText from Sampletemp;
>                  ^
> HINT:  No function matches the given name and argument types. You may need
> to add explicit type casts.
> 4. select sum(SampleText :: int) as SampleText from Sampletemp;
> ERROR:  invalid input syntax for integer: "Mr. Raja"
> I am getting error while selecting using some aggregation.
> I would like to sum up these values, if it has TEXT (example Name)that can
> be ZERO while querying. I need answer as 4055.
> Can i have your suggestion/solutions please?

SELECT sum(CASE WHEN sampletext ~ e'^\\d+$' THEN sampletext::int ELSE
0 END) FROM ..

Regards

Pavel Stehule

p.s. better to use a two columns

>
> Regards,
> Vikram A