Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using PL/SQL to determine what letter a variable begins with

431901Sep 6 2005 — edited Sep 6 2005
Hello again,

I have the following code with a select query and a loop which fetchs the data from the query into the variables v_var and v_var2. What I would like to do is test v_var2 to determine what letter it starts with. Then I would like to use PL/SQL and HTML to create an anchor. So there would be the letters of the alphabet at the top of the page and the user would be able to click on one of the letters and be taken to the COMPANY_NAMEs that start with that letter.
------------------------------------------------------------------------------------------------
v_sql := 'SELECT REFERENCE_NO, COMPANY_NAME
FROM CONSULTANTS
ORDER BY COMPANY_NAME';
--------------------------------------------------
HTP.print ('<tr>');
HTP.print ('<th align="center" bgcolor="#EFEFEF"><span class="style5">Company Name</span></th>');
HTP.print ('</tr>');

OPEN cv FOR v_sql;
LOOP
FETCH cv INTO v_var, v_var2;
EXIT WHEN cv%NOTFOUND;

HTP.print ('<tr><td align="left"');
--------------------------------------------------
IF MOD (alternatingrey, 2) = 0
THEN
HTP.print ('bgcolor="#EFEFEF"');
END IF;
--------------------------------------------------
HTP.prn ('><span class="style1">');

-- IF v_var2 starts with an A then HTML A linked to above alphabetical anchor

HTP.print (v_var2);
----------------------------------------------
IF v_var2 is NULL
THEN HTP.prn (v_var);
END IF;
----------------------------------------------
HTP.print (' </span>
');
HTP.print ('</td>');
HTP.print ('</tr>');
--------------------------------------------------
alternatingrey := alternatingrey + 1;
--------------------------------------------------
END LOOP;
CLOSE cv;
------------------------------------------------------------------------------
Thanks

Doug
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 4 2005
Added on Sep 6 2005
2 comments
448 views