Home » SQL & PL/SQL » SQL & PL/SQL » Upper Function
icon9.gif  Upper Function [message #410257] Thu, 25 June 2009 21:49 Go to next message
shoowaruno
Messages: 14
Registered: February 2009
Junior Member
Hi all

Is there any other option to make the input of the user to UPPER CASE?

SELECT pin FROM pintable WHERE lastname LIKE UPPER('sison');

Re: Upper Function [message #410258 is a reply to message #410257] Thu, 25 June 2009 22:04 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
shoowaruno wrote on Thu, 25 June 2009 19:49
Hi all

Is there any other option to make the input of the user to UPPER CASE?

SELECT pin FROM pintable WHERE lastname LIKE UPPER('sison');




No, what is wrong with you using UPPER()?
Re: Upper Function [message #410285 is a reply to message #410257] Fri, 26 June 2009 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any other option to make the input of the user to UPPER CASE?

* Ensure the client program convert the input in upper case.
* Add a check constraint on your column to enforce upper case input.
* Add a trigger on insert/update to convert the value to upper case.

Regards
Michel

[Updated on: Fri, 26 June 2009 00:15]

Report message to a moderator

Re: Upper Function [message #410293 is a reply to message #410257] Fri, 26 June 2009 00:52 Go to previous messageGo to next message
wmgonzalbo
Messages: 97
Registered: November 2008
Member
As mentioned above..

I think setting the text case should be a front-end application's 'problem', for PL/SQL, yes UPPER() is the only way to go.. Unless doing it with some lengthy/inefficient coding just like this..


set serveroutput on;

DECLARE

 TYPE v_array IS VARRAY(100) OF VARCHAR2(1);
 va_word v_array;

 v_word  VARCHAR(100 BYTE) := 'Or@cle!z FAq'; -- input word
 v_len   NUMBER;
 v_conv  VARCHAR(1 BYTE);
 v_ascii NUMBER;
 
 
BEGIN

 va_word := v_array('w');

 SELECT LENGTH(v_word) INTO v_len FROM DUAL;
 
 FOR i in 1 .. v_len LOOP
 
  SELECT substr (v_word,i,1) INTO va_word(i) FROM dual;  

  va_word.extend;

  If ASCII(va_word(i)) BETWEEN 97 AND 122 Then
  
   SELECT ASCII(va_word(i)) INTO v_ascii FROM DUAL;
  
   SELECT TO_SINGLE_BYTE(CHR(15711200+v_ascii)) INTO va_word(i) FROM DUAL; 
  
  End if;
  
  dbms_output.put_line (va_word(i));
 
 END LOOP;
 
END;

-- in a linear form for easier reading --

OR@CLE!Z FAQ

PL/SQL procedure successfully completed.



Yet it still uses functions.. so why not use UPPER()?

Regards,
Wilbert

[Updated on: Fri, 26 June 2009 01:04]

Report message to a moderator

Re: Upper Function [message #410353 is a reply to message #410293] Fri, 26 June 2009 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
WTF?

I think the question was asking if there was another way to do the comparison, rather than 'Is there another way to convert a string into upper case?'

even so, you could pick up a few tips:

 SELECT LENGTH(v_word) INTO v_len FROM DUAL;
Avg time to execute on my system: 0.0064 seconds

v_len := length(v_word);
Avg time to execute on my system: 0.00015 seconds

Never use Select From dual to execute functions in pl/sql unless you're using weird things like pipelined functions.

Why are you bothering with TO_SINGLE_BYTE, when you could just do CHR(v_ascii-32) - I doubt there's much performance difference, but one of them is slightly more intuituve than the other.
Re: Upper Function [message #410380 is a reply to message #410353] Fri, 26 June 2009 12:54 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Fri, 26 June 2009 09:39
WTF?

I think the question was asking if there was another way to do the comparison, rather than 'Is there another way to convert a string into upper case?'



Actually, I was going to make the same suggestion as wmgonzalbo, or have the user look into setting NLS_SORT=BINARY_CI.

Then I reread the question and wasn't quite sure what the OP was getting at, so I decided against posting either suggestion.
Re: Upper Function [message #410640 is a reply to message #410380] Mon, 29 June 2009 07:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fair enough - must be me then.

Smile
Previous Topic: Export CSV to Chinese characters
Next Topic: ORA-24374: define not done before fetch or execute and fetch
Goto Forum:
  


Current Time: Thu Oct 02 02:32:44 CDT 2014

Total time taken to generate the page: 0.18520 seconds