Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

SUBSTRING in a number

edited August 2017 in General Discussion

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?

Comments

  • 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

  • thanks ,but is problen field es number .
    Good day

  • 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

  • Perfect ,thanks so much

  • marcothesanemarcothesane Administrator

    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.

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.