Home » SQL & PL/SQL » SQL & PL/SQL » SQL: returning selected characters in a string to a variable
SQL: returning selected characters in a string to a variable [message #261017] Tue, 21 August 2007 09:29 Go to next message
samismyname
Messages: 4
Registered: August 2007
Junior Member
I used the ACCEPT function in SQL to get user input from the prompt and storing it as a CHAR in the X_STRING variable. I want to take the last 8 characters of this 10 character variable and storing that in the Y_STRING variable. I've tried

select (&X_STRING, 8 ) as Y_STRING

but this doesn't seem to return a result to the Y_STRING variable. Please help. Thanks
Re: SQL: returning selected characters in a string to a variable [message #261023 is a reply to message #261017] Tue, 21 August 2007 09:37 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Hi
It would probably have been easier to actually post your proc, however if you look at the substr function it should help
Connected.
SQL> select substr('this is a string',-8) from dual;

SUBSTR('
--------
a string
Re: SQL: returning selected characters in a string to a variable [message #261037 is a reply to message #261017] Tue, 21 August 2007 09:56 Go to previous messageGo to next message
samismyname
Messages: 4
Registered: August 2007
Junior Member
Hi, Thanks that seemed to return the part I wanted from the string however, I was wondering if I could get the string as a variable (in my case the variable name would be, date_month) rather than returning the result.

I have got this code

select sum (NB) as ENR_Month_GAT
from ENROLMENT_PER_DAY_COUNT
where TODAY like CONCAT(CONCAT('%','&date_month'),'%')

the date_month variable should contain the string /08/2007 from the original string 13/08/2007 (therefore, the last 8 characters) so that when it is concatinated forms the string '%/08/2007%%'.
Thanks
Re: SQL: returning selected characters in a string to a variable [message #261038 is a reply to message #261017] Tue, 21 August 2007 09:58 Go to previous messageGo to next message
samismyname
Messages: 4
Registered: August 2007
Junior Member
sorry, the resulting string should be '%/08/2007%' instead of '%/08/2007%%' in the previous post.
Re: SQL: returning selected characters in a string to a variable [message #261062 is a reply to message #261017] Tue, 21 August 2007 12:46 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
column y_string new_value y_string noprint

select substr('this is a string',-8) y_string
from dual;

select '&y_string' from dual;
Re: SQL: returning selected characters in a string to a variable [message #261266 is a reply to message #261017] Wed, 22 August 2007 04:23 Go to previous message
samismyname
Messages: 4
Registered: August 2007
Junior Member
That worked.
Great help, Thanks
Previous Topic: Is it possible to use a variable with the UNISTR function? (renamed by LF)
Next Topic: How to print all child tables for a parent table?
Goto Forum:
  


Current Time: Thu Dec 08 14:19:51 CST 2016

Total time taken to generate the page: 0.15969 seconds