CODEWITHSUNDEEP
sqloracle-databaseplsqlmask
Thiagoab
Thiagoab

Reputation: 23

PL/SQL column mask with fixed number of characters in queries

I'd like to write a query so its output would be a well formated text. Each of its columns would be a specific number of characters wide, if the column's value returned by the query is smaller then the columns number of characters, the remaining characters would be informed as "0" if the column represents a number or " " if the column represents a string. Is it possible?

Thanks,

Thiago

Upvotes: 1

Views: 1987

Answers (1)

Jeff Young
Jeff Young

Reputation: 140

Well, the experts have warned you against it, but here it is:

select   case when REGEXP_replace (DUMP (varchar2_column), '^Typ=(\d+).*', '\1') = 1 then lpad(varchar2_column, 10, ' ')
          else null end varchar2_column,
         case when REGEXP_replace (DUMP (number_column), '^Typ=(\d+).*', '\1') = 2 then lpad(number_column, 10, 0)
          else null end number_column
  from   table

data types (from dump): http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#BABCGCHG

oracle regular expressions: http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm

I would post the link for the dump function, but my reputation, is, well... You can Google it.

Upvotes: 1

Related Questions