Search code examples
sqloraclebi-publisher

SQL QUERY to get preceding digits as 0 if the number is not 6 digit

If the POSITION_ID is not 6 digit I want the preceding spaces to be 0

For example-

1102 should be 001102, 304 should be 000302

For this i used the below query -

  select LPAD(REGEXP_REPLACE(position_id,'\D+'),6,'0')  test from dual

But this query is adding 2 in front of numbers that are already 6.

For example 110092 is coming as 211009 345679 is coming as 234567 106698 is coming as 210669

How to correct the query I have used above

Solution

  • The function to get a formatted string from a number in Oracle is TO_CHAR. The format to get a six-digit integer is 'FM000000'.

    SELECT TO_CHAR(position_id, 'FM000000') FROM mytable;

    Docs: TO_CHAR, Number format models, FM