string convertion [message #271899] |
Wed, 03 October 2007 05:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
brintha
Messages: 28 Registered: August 2007
|
Junior Member |
|
|
hi,
can any one help me in solving my issue... I have a userparameter and if its value is 'Begins with~xxx' i have to append % at the end and check that with my query. But it is not working. The following is the code i used.
function CF_1Formula return CHAR is
S_CHAR VARCHAR(200):='AAA';
P_CHAR VARCHAR(200):='AAA';
NUM_COUNT NUMBER;
begin
SELECT LTRIM((REPLACE(REPLACE(:P_DUMMY_COUNTRY,'BEGINS WITH~',''),'''',''))||'%') INTO S_CHAR FROM DUAL;
SELECT COUNT(DISTINCT SHIPMENT_GID)INTO NUM_COUNT FROM SHIPMENT WHERE SHIPMENT_GID LIKE TO_CHAR(S_CHAR);
RETURN NUM_COUNT;
end;
all spaces are removed in the first select code and % is appended at the end and this value is used in the where condition of the 2nd query. But the like is not working and count is zero.
regards,
brintha
|
|
|
|
Re: string convertion [message #271951 is a reply to message #271899] |
Wed, 03 October 2007 08:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
brintha
Messages: 28 Registered: August 2007
|
Junior Member |
|
|
HI,
what u understood is not exact the way how my doubt is. this is what i want:
FUNCTION cf_1
RETUN NUMBER
IS
num_count NUMBER := 0;
BEGIN
IF :p_dummy_country = 'BEGINS WITH ~test'
THEN
SELECT COUNT(DISTINCT shipment_gid)
INTO num_count
FROM shipment
WHERE shipment_gid LIKE 'test%';
END IF;
RETURN (num_count);
END;
this is how i want., csn u please help me out...
[Updated on: Wed, 03 October 2007 08:04] Report message to a moderator
|
|
|
Re: string convertion [message #271991 is a reply to message #271951] |
Wed, 03 October 2007 10:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Please, read the OraFAQ Forum Guide to learn how to properly format a message.
Now I don't follow you; are you saying that the beginning of the string is 'BEGINS WITH ~', while the rest of it may be anything (for example, 'BEGINS WITH ~TEST' or 'BEGINS WITH ~LITTLEFOOT HAS LITTLE FOOT' etc.)?
If so, test for SUBSTR(:p_dummy_country, 1, 13) = 'BEGINS WITH ~'
[Updated on: Wed, 03 October 2007 10:36] Report message to a moderator
|
|
|