PL/SQL character variable

Question

All Items in my table are 7 digits. If I input something with 6 digits should show 0 at the beginning. (output should be 7 digits.)

For example:

Input= 123456 Output= 0123456

How can I write it in Oracle SQL?

I've tried that. But it does not work.

cursor c1 is
into art_no_tmp1
select  art_no
from    barticles b
where   b.ean_no_1 = '789546584587';

cursor checklength is
into len_number
select length(art_no_tmp1)
from    dual;


if(len_number = 6) then
      return '0'+art_no_tmp1;
else
      return art_no_tmp1;
end if;

does anyone have an idea?

For future reference - the string concatenation operator in Oracle SQL and PL/SQL is ||, not + - thus, you'd want to write return '0' || ART_NO_TMP1;. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Sep 5, 2013 at 14:00

Vincent Malgrat · Accepted Answer · 2013-09-05 13:24:05Z

5

Use TO_CHAR, with the leading 0 format:

SQL> WITH DATA AS (
  2     SELECT 1       num FROM DUAL UNION ALL
  3     SELECT 12      num FROM DUAL UNION ALL
  4     SELECT 123     num FROM DUAL UNION ALL
  5     SELECT 1234    num FROM DUAL UNION ALL
  6     SELECT 12345   num FROM DUAL UNION ALL
  7     SELECT 123456  num FROM DUAL UNION ALL
  8     SELECT 1234567 num FROM DUAL
  9  )
 10  SELECT num, to_char(num, '0000000') FROM data;

       NUM TO_CHAR(NUM,'0000000')
---------- ----------------------
         1  0000001
        12  0000012
       123  0000123
      1234  0001234
     12345  0012345
    123456  0123456
   1234567  1234567

answered Sep 5, 2013 at 13:24

Vincent Malgrat

68k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jagath Banneheka Over a year ago

Thanx Vincent Malgrat... it has worked! have found also other way.. return '000'||art_no_tmp1;

user272735 Over a year ago

@JagathBanneheka No you have not found other way - just use to_char as shown by Vincent.

Roger Over a year ago

Will this behave the same as my answer? truncating the string to the length in the second argument?

Vincent Malgrat Over a year ago

@roger I think the final function should raise an exception when the input is larger than 7 characters, whichever method is chosen. Silently truncating may make the caller think that everything went well. LPAD seems to give the same result as TO_CHAR, although it uses an implicit conversion if the input is of type number.

Roger Over a year ago

I agree: "Silently truncating may make the caller think that everything went well.". OP says all his data is 7 digits long, so his column is probably defined as NUMBER(7).

Roger · Accepted Answer · 2013-09-05 13:57:54Z

3

How about the lpad function?

LPAD('123', 7, '0') = '0000123'

Be careful though when the string is shorther than the length you want to fix.

Take a look at this example. http://sqlfiddle.com/#!4/3ae17/2

edited Sep 5, 2013 at 13:57

answered Sep 5, 2013 at 13:48

Roger

2,9624 gold badges32 silver badges40 bronze badges

2 Comments

David Aldridge Over a year ago

I'd go for the to_char method, as this involves an implicit to_char anyway.

Roger Over a year ago

@DavidAldridge Yeah, but when the length of the string gets bigger, it just sucks that it yields #######, take a look at the fiddle.

Collectives™ on Stack Overflow

PL/SQL character variable

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related