SUBSTRING in a number

ismael

Hi .
I am useing substring with a field number and this not ok.

I wold like use a function for a field number.

My field is f1=130001
I want get f1=13

Is possible?

RaghavA

hi,

If you know that you always need first 2 characters out of your string "f1" then you can use left function.

dbadmin=> select left('130001',2);

left

13
(1 row)

Regards,
Raghav Agrawal

ismael

thanks ,but is problen field es number .
Good day

RaghavA

Yes, you are correct. i had to cast the datatype into char type.

dbadmin=> create table t1 ( a1 varchar(50),f1 number(20));
CREATE TABLE
dbadmin=> insert into t1 values ('aabbccddeeffgghhiijj',1234567890);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> select * from t1;
          a1          |     f1
----------------------+------------
 aabbccddeeffgghhiijj | 1234567890
(1 row)
dbadmin=> select left(a1,2), left(f1::varchar,2) from t1;
 left | left
------+------
 aa   | 12
(1 row)

OR

dbadmin=> select substr(a1,1,2), substr(f1::varchar,1,2) from t1;
 substr | substr
--------+--------
 aa     | 12
(1 row)

Regards

ismael

Perfect ,thanks so much

marcothesane

But: do you need the result as numeric, or as string?
If you remain numeric, you should not perform string operations on numbers.
If I need to get 13 out of 130001, I would go 130001 // 10000 - that's an integer division by 10000.

ismael

I need numeric because this field the use in other tables later and are a lot of tables.
Thanks.