| how to use substr while assigning the column & using length function [message #615622] |
Fri, 06 June 2014 03:35  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
1) order_number vachar2(5);
2)I assigned the xxc_number column from the another tables column into the order_number column like order_number :=xxc_number ;
But the abc column had 'defghij'(7 letters)
So we get ORA-06502: PL/SQL: numeric or value error: character string buffer too small error right?
I don't want to increase the column data type size instead i need to use substr , how can i do this? Please explain.
I know to do this using using substr(xxc_number ,1,5), but if the xxc_number has defghij(size is 7) means i need to show fully column name , but the substr is defgh right?
**How could i know the xxc_number column has that much sizes ...
For this we use length function, i don't how to do this please help me.
Thanks
|
|
|
|
|
|
| Re: how to use substr while assigning the column & using length function [message #615625 is a reply to message #615622] |
Fri, 06 June 2014 03:46  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You don't know how to use the length function?
Have you looked it up in the documentation? Not that you should need to, it's a really basic function.
I'm not sure what you mean by this:
Quote:
but if the xxc_number has defghij(size is 7) means i need to show fully column name
Why aren't you just always using substr, if you always need 5 chars that's the way to do it.
|
|
|
|
| Re: how to use substr while assigning the column & using length function [message #615627 is a reply to message #615622] |
Fri, 06 June 2014 03:58 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
What is the question here? So many sentences seems confusing.
Do you want order_number to take all the values of xxc_number irrespective of the size? If yes then just declare it as :
order_number table_name.xxc_number%TYPE
If you are not willing to the above, then you need to handle all the cases where length of xxc_number exceeds 5. The only option is to use SUBSTR. Something like :
IF LENGTH(xxc_number) <=5
THEN
order_number := xxc_number;
ELSE
<do something>
END IF;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
