Home » SQL & PL/SQL » SQL & PL/SQL » New Guy With Question
New Guy With Question [message #236346] Wed, 09 May 2007 12:31 Go to next message
Messages: 4
Registered: May 2007
Location: St. Louis, Missouri USA
Junior Member
Howdy all.

I'm getting into Oracle programming, but I'm in a bind. I have a list of business names (vendors). With this list, I want to create a "cursor for" loop, to create a "vendor code." The vendor code must be: a) The first three characters of the vendor (weeding out punctuation and spaces), b) a four-digit sequential number, after the codes are sorted (eg: WAM0001, WAM0002, WAM0003, etc.)

For the life of me, I can't figure out how to weed out the unwanted characters in "a," and then get a sequence in "b." If anyone can at least point me in the right direction, it would be extremely helpful.

Re: New Guy With Question [message #236355 is a reply to message #236346] Wed, 09 May 2007 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 65144
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course, this is homework.
So start to post what you tried and we'll help you.
Oracle version is mandatory as solution depends on it.

Btw, I assume this a PL/SQL exercise otherwise there is no need of PL/SQL.

You can have a look at:
PL/SQL User's Guide and Reference, there is a section Managing Cursors in PL/SQL
SQL Reference to see what you can do with ROWNUM Pseudocolumn or ROW_NUMBER function and how to concatenate strings and convert number to char with a specific format.

Re: New Guy With Question [message #236505 is a reply to message #236355] Thu, 10 May 2007 02:16 Go to previous message
Messages: 7062
Registered: December 2001
Senior Member
Michel has an excellent point: you should give something for us to start with. From what I understand you are trying to generate company codes from the company names. You can do so in a single SQL statement. Again, Michel has given you a lot of hints. If you reply with your attempt, I'll post my solution, it is pure SQL. I've used the following built-ins:
  • SUBSTR: We only need 3 characters from the company name. But what happens if the name is only 2 characters?
  • UPPER: It is unclear whether codes can be mixed case. In my solution it's all upper case.
  • REGEXP_REPLACE --> provided that you have Oracle 10g. It is useful to strip special characters
  • ROW_NUMBER() analytic function.
  • TO_CHAR + an explicit numeric format to convert the row_number() to a char

I've given you a lot, so has Michel. Let's see what you can make from this. Good Luck!


[Updated on: Thu, 10 May 2007 02:16]

Report message to a moderator

Previous Topic: Nth record
Next Topic: Replication, Streams, Merge or MVs?
Goto Forum:

Current Time: Sun Aug 20 21:52:32 CDT 2017

Total time taken to generate the page: 0.05190 seconds