Set plsql parameters partially

Question

I need to create a query that when i enter partially the value of a parameter until"/" is shown it displays all the row sequences with that parameter in the first part before "/"

I enter as a parameter: pwd

The result of query should be: pwd/1 pwd/2 pwd/3....

@KieranQuinn @pwd is not valid Oracle syntax. Were you meaning to use a bind variable :pwd or a substitution variable &pwd or just a PL/SQL variable pwd? — MT0
– MT0, Commented Aug 29, 2019 at 11:58

MT0 · Accepted Answer · 2019-08-29 12:09:03Z

2

If you have a bind variable :parameter then:

SELECT *
FROM   table_name
WHERE  value LIKE :parameter || '/%'

So for some test data:

CREATE TABLE table_name ( id, value ) AS
  SELECT 1, 'pwd/1'     FROM DUAL UNION ALL
  SELECT 2, 'pwd/2'     FROM DUAL UNION ALL
  SELECT 3, 'pwdtest/1' FROM DUAL;

If :parameter is pwd then the output is:

ID | VALUE
-: | :----
 1 | pwd/1
 2 | pwd/2

db<>fiddle here

edited Aug 29, 2019 at 12:09

answered Aug 29, 2019 at 12:02

MT0

173k12 gold badges69 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Set plsql parameters partially

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related