How to get substring of numeric value

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Serman D.
    #1

    How to get substring of numeric value

    I would like to extract the first 6 digits of a numeric value (e.g.
    the string '123456' out of the numeric 123456789012345 6789). I tried
    a combination of CAST and SUBSTR, but it seems I am not doing the
    conversion properly. Please advice:

    $ cat substr.sql
    connect to viper
    @

    create table largenum (id integer not null, lval decimal(21,0) not
    null)
    @

    insert into largenum values (1, 123456789012345 6789)
    @

    select lval from largenum
    @

    select substr(cast(lva l as char), 1, 6) from largenum
    @

    drop table largenum
    @

    connect reset
    @

    terminate
    @

    $ db2 -td@ -f substr.sql

    Database Connection Information

    Database server = DB2/LINUX 9.5.0
    SQL authorization ID = DB2INST3
    Local database alias = VIPER


    DB20000I The SQL command completed successfully.

    DB20000I The SQL command completed successfully.


    LVAL
    -----------------------
    123456789012345 6789.

    1 record(s) selected.


    SQL0138N A numeric argument of a built-in string function is out of
    range.
    SQLSTATE=22011

    DB20000I The SQL command completed successfully.

    DB20000I The SQL command completed successfully.

    DB20000I The TERMINATE command completed successfully.

    --
    Tags: None
    • Lennart
      #2
      Re: How to get substring of numeric value

      On Apr 9, 2:42 pm, "Serman D." <serma...@hotma il.comwrote:
      I would like to extract the first 6 digits of a numeric value (e.g.
      the string '123456' out of the numeric 123456789012345 6789). I tried
      a combination of CAST and SUBSTR, but it seems I am not doing the
      conversion properly. Please advice:
      >
      $ cat substr.sql
      connect to viper
      @
      >
      create table largenum (id integer not null, lval decimal(21,0) not
      null)
      @
      >
      insert into largenum values (1, 123456789012345 6789)
      @
      >
      select lval from largenum
      @
      >
      select substr(cast(lva l as char), 1, 6) from largenum
      @
      >
      drop table largenum
      @
      >
      connect reset
      @
      >
      terminate
      @
      >
      $ db2 -td@ -f substr.sql
      >
      Database Connection Information
      >
      Database server = DB2/LINUX 9.5.0
      SQL authorization ID = DB2INST3
      Local database alias = VIPER
      >
      DB20000I The SQL command completed successfully.
      >
      DB20000I The SQL command completed successfully.
      >
      LVAL
      -----------------------
      123456789012345 6789.
      >
      1 record(s) selected.
      >
      SQL0138N A numeric argument of a built-in string function is out of
      range.
      SQLSTATE=22011
      >
      DB20000I The SQL command completed successfully.
      >
      DB20000I The SQL command completed successfully.
      >
      DB20000I The TERMINATE command completed successfully.
      >
      --
      You need to cast it to something bigger than a single char. I.e.:

      db2 "select substr(cast(lva l as char(22)),3,6) from largenum"

      1
      ------
      123456

      1 record(s) selected.

      /Lennart

        Comment

        • Serman D.
          #3
          Re: How to get substring of numeric value

          On Apr 9, 3:20 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
          db2 "select substr(cast(lva l as char(22)),3,6) from largenum"
          Thank you for your replay Lennart. I have some follow-up questions:

          1. Why are leading zeros added in the cast from numeric to char?
          2. How do I reliably remove them?
          3. Consider the below sample: What query returns the string '123456'
          for the all rows?

          create table largenum (lval decimal(21,0) not null)
          @

          insert into largenum values
          (12345678901234 56789),
          ( 123456789012345 678),
          ( 123456789012345 67),
          ( 123456789012345 6),
          ( 123456789012345 ),
          ( 12345678901234) ,
          ( 1234567890123),
          ( 123456789012)
          @

          select substr(cast(lva l as char(64)), 3, 6) as as_char from largenum
          @

          AS_CHAR
          -------
          123456
          012345
          001234
          000123
          000012
          000001
          000000
          000000

          8 record(s) selected.

          --

            Comment

            • fynn00@googlemail.com
              #4
              Re: How to get substring of numeric value

              your previous code indicates you are on Version 9
              Database server = DB2/LINUX 9.5.0
              SQL authorization ID = DB2INST3
              Local database alias = VIPER
              Did you try the STRIP function already?

              db2 "values(STRIP(' 00123',L,'0'))"

              1
              -----
              123


              Regards,
              Florian

                Comment

                • Lennart
                  #5
                  Re: How to get substring of numeric value

                  On Apr 10, 9:22 am, "Serman D." <serma...@hotma il.comwrote:
                  On Apr 9, 3:20 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
                  >
                  db2 "select substr(cast(lva l as char(22)),3,6) from largenum"
                  >
                  Thank you for your replay Lennart. I have some follow-up questions:
                  >
                  1. Why are leading zeros added in the cast from numeric to char?
                  2. How do I reliably remove them?
                  3. Consider the below sample: What query returns the string '123456'
                  for the all rows?
                  >
                  create table largenum (lval decimal(21,0) not null)
                  @
                  >
                  insert into largenum values
                  (12345678901234 56789),
                  ( 123456789012345 678),
                  ( 123456789012345 67),
                  ( 123456789012345 6),
                  ( 123456789012345 ),
                  ( 12345678901234) ,
                  ( 1234567890123),
                  ( 123456789012)
                  @
                  >
                  select substr(cast(lva l as char(64)), 3, 6) as as_char from largenum
                  @
                  >
                  AS_CHAR
                  -------
                  123456
                  012345
                  001234
                  000123
                  000012
                  000001
                  000000
                  000000
                  >
                  8 record(s) selected.
                  >
                  --
                  Something like:

                  select substr(cast(lva l as char(22)), 22 - length(strip(ca st(lval as
                  char(22)), L, '0')) + 1, 6) as as_char from largenum"

                  AS_CHAR
                  -------
                  123456
                  123456
                  123456
                  123456
                  123456
                  123456
                  123456
                  123456

                  /Lennart

                    Comment

                    Previous template Next
                    Working...