tomaz108
asked on
how to use variables in sql
I'm beginner in Oracle. I'm wondering how to use variables in SQL, to make statement less complicated. For example:
SELECT substr(nnp,instr(nnp, '*')+1, instr(nnp, '/')+1 - instr(nnp, '*')+1)FROM dav_5;
i want to make like this:
A := instr(nnp, '*')+1
B := instr(nnp, '/')+1
SELECT SUBSTR (nnp, A, B-A)
FROM dav_5
What should I do, to run this in SQL Plus?
SELECT substr(nnp,instr(nnp, '*')+1, instr(nnp, '/')+1 - instr(nnp, '*')+1)FROM dav_5;
i want to make like this:
A := instr(nnp, '*')+1
B := instr(nnp, '/')+1
SELECT SUBSTR (nnp, A, B-A)
FROM dav_5
What should I do, to run this in SQL Plus?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As your variable containts single quotes, you need to define them with enclosing double quotes
Hi
You need to use Define to declare variables which can be used in SQL.
you just give the comman DEFINE to see what all variable are already declared.
SQL> DEFINE A
SQL> DEFINE B
SQL> ACCEPT A CHAR PROMPT 'Enter Value of First Expression'
SQL> ACCEPT B CHAR PROMPT 'Enter value of second Expression'
SQL> SELECT SUBSTR (nnp, &A, &B-&A) FROM dav_5
kamal
You need to use Define to declare variables which can be used in SQL.
you just give the comman DEFINE to see what all variable are already declared.
SQL> DEFINE A
SQL> DEFINE B
SQL> ACCEPT A CHAR PROMPT 'Enter Value of First Expression'
SQL> ACCEPT B CHAR PROMPT 'Enter value of second Expression'
SQL> SELECT SUBSTR (nnp, &A, &B-&A) FROM dav_5
kamal