SUBSTRING in a number

Comments

• RaghavA

  August 2017

  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

  0
• ismael

  August 2017

  thanks ,but is problen field es number .
  Good day

  0
• RaghavA

  August 2017

  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

  0
• ismael

  August 2017

  Perfect ,thanks so much

  1
• marcothesane - Select Field - Administrator

  August 2017

  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.

  1
• ismael

  August 2017

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

  0