7 Replies Latest reply on Jun 27, 2008 10:38 PM by Aketi Jyuuzou

    Select query

    user7988
      How to display with zeros if some digits in ssn are empty
      for example
      -12-1234
      111- -2222
      111-11-
      should be displayed as 000-12-1234
      111-00-2222
      111-11-0000

      Thanks
        • 1. Re: Select query with SSN
          Chaitanya.S.S.K
          Crude way :
          SQL> with t as(
            2      select '-12-1234' ssn from dual union
            3      select '111- -2222' from dual union
            4      select '111-11-' from dual)
            5  --
            6  select to_char(nvl(trim(substr(ssn,1,instr(ssn,'-')-1)),0),'FM099')
            7          ||'-'
            8          || to_char(nvl(trim(substr(ssn,instr(ssn,'-')+1,instr(ssn,'-',1,2)-instr(ssn,'-')-1)),0),'FM09') 
            9          ||'-'
           10          || to_char(nvl(trim(substr(ssn,instr(ssn,'-',1,2)+1)),0),'FM0999') ssn 
           11  from t;
          
          SSN
          --------------
          000-12-1234
          111-00-2222
          111-11-0000
          • 2. Re: Select query
            Rob van Wijk
            You seem to have modeled three different attributes into one, which leads to all kinds of substr's and instr's complexing your code.

            See the difference:
            SQL> create table mytable_bad_design(ssn)
              2  as
              3  select '-12-1234' from dual union all
              4  select '111- -2222' from dual union all
              5  select '111-11-' from dual
              6  /

            Tabel is aangemaakt.

            SQL> create table mytable_good_design(ssn1,ssn2,ssn3)
              2  as
              3  select null, 12, 1234 from dual union all
              4  select 111, null, 2222 from dual union all
              5  select 111, 11, null from dual
              6  /

            Tabel is aangemaakt.

            SQL> select to_char(nvl(to_number(trim(substr(ssn,1,instr(ssn,'-')-1))),0),'fm000') || '-' ||
              2         to_char(nvl(to_number(trim(substr(ssn,instr(ssn,'-')+1,instr(ssn,'-',1,2)-instr(ssn,'-')-1))),0),'fm00') || '-' ||
              3         to_char(nvl(to_number(trim(substr(ssn,instr(ssn,'-',1,2)+1))),0),'fm0000') ssn
              4    from mytable_bad_design
              5  /

            SSN
            --------------
            000-12-1234
            111-00-2222
            111-11-0000

            3 rijen zijn geselecteerd.

            SQL> select to_char(nvl(ssn1,0),'fm000') || '-' ||
              2         to_char(nvl(ssn2,0),'fm00') || '-' ||
              3         to_char(nvl(ssn3,0),'fm0000') ssn
              4    from mytable_good_design
              5  /

            SSN
            --------------
            000-12-1234
            111-00-2222
            111-11-0000

            3 rijen zijn geselecteerd.
            Regards,
            Rob.
            • 3. Re: Select query
              MichaelS
              In 10g:
              SQL>  with t as(
                      select '-12-1234' ssn from dual union
                      select '111- -2222' from dual union
                      select '111-11-' from dual)
              select ssn,
                     regexp_replace(regexp_replace(regexp_replace(ssn,'^-','000-'),'-.-','-00-'),'-$','-0000') ssn2
               from t
              
              SSN                  SSN2                
              -------------------- --------------------
              -12-1234             000-12-1234         
              111- -2222           111-00-2222         
              111-11-              111-11-0000         
              
              3 rows selected.
              • 4. Re: Select query
                user7988
                Thanks a lot

                I am getting this error ORA-00904: "REGEXP_REPLACE": invalid identifier
                May be because i am working on 9i.
                • 5. Re: Select query
                  user7988
                  Right now i am converting the ssn 123456789 from this format to 123-45-6789 using a function in which i have the below sql statement.

                  select nvl(substr(ssn,1,3),'000')||'-'||nvl(substr(ssn,4,2),'00')||'-'||nvl(substr(ssn,6),'0000') from DUAL;

                  How can i do both the task(converting into ssn format and replacing the missing digits with Zeros) in the same above query?
                  Any suggestions please
                  Thank a lot
                  • 6. Re: Select query
                    Rob van Wijk
                    So you store it as one number. If it contains the number 123456, how do you know where the zeros have to be filled in? In other words, is this 123-45-0006, or 000-12-3456, or 012-03-0456, or ...

                    Regards,
                    Rob.
                    • 7. Re: Select query
                      Aketi Jyuuzou
                      col ssn2 for a20
                      with t as(
                      select '-12-1234' ssn from dual union
                      select '111- -2222' from dual union
                      select '111-11-' from dual)
                      select ssn,
                      RegExp_Replace(
                      Replace(
                      RegExp_Replace(ssn,'^-' ,'000-' )
                                        ,'- -','-00-' )
                                        ,'-$' ,'-0000') as ssn2
                      from t;
                      SSN         SSN2
                      ----------  -----------
                      -12-1234    000-12-1234
                      111- -2222  111-00-2222
                      111-11-     111-11-0000
                      Oh you use Oracle9i.

                      on Oracle9i,
                      We can use regex with using owa_pattern.