1

I have a table with employee names and their wages. Now I want to concatenate them in a column. So far so good.

But instead of the normal number (ex. $1200) it needs to show me in the query a star (*) for every $100.

The result should look like this:

Employees and their wage
Smith, **********
Jonson, ******************
Sanchez, *************

I have searched everywhere with every search term which came to my mind. Can it be that it is not possible in Oracle SQL Developer? I use database 11g.

I would be happy if someone could direct me to the right answer. Thanks in advance.

PzFPadu
  • 13
  • 3
  • 1
    For salary of 1200, you need 12 asterisks, that is clear. However, what if the salary is 1180? Do you truncate (round down) to 11, do you round up to 12, or do you simply round (so 1130 means 11 asterisks, but 1180 means 12 asterisks)? Obviously you can't have 11.8 asterisks. –  Aug 24 '18 at 00:19
  • What's the point in doing that? I'd understand if you wanted to hide someone's salary, but - replacing every $100 with an asterisk is *doubtful*. It is obvious that Smith earns $1000 or Jonson $1800. A few dollars difference doesn't make a *difference*. – Littlefoot Aug 24 '18 at 06:44
  • @Littlefoot, I think the purpose is not to hide data but to build a text-based bar graph. Mind you, since the base of the graph is not flat it will have limited utility in this case, but that could be fixed with some padding after the employee surnames. – Jeffrey Kemp Aug 27 '18 at 07:38
  • That makes sense, @Jeffrey. That's what we used *ages* ago, when there was no GUI. If they still use it, fine with me. I'm taking my objection back. Thank you for the comment! – Littlefoot Aug 27 '18 at 08:36

1 Answers1

1

Given a salary value you can create a string of asterisks for every $100 dollars of salary with

RPAD('*', TRUNC(SALARY/100), '*')

SQLfiddle here

Best of luck.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • `TRUNC` is not even needed, `RPAD` will do that internally anyway. (And it does truncate, it doesn't round or round up.) –  Aug 24 '18 at 00:35