0

In an SQL script which I have inherited, there the following where line, which I partially do not understand.

Where substr(hou.name,1,2) like '&Region%'

I understand the substr function, but I do not understand the &Region in the like clause.

Can anybody help me make sense of it?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Barattolo_67
  • 79
  • 1
  • 1
  • 11
  • 2
    If your SQL environment is an Oracle one, then "& is the variable prefix in sqlplus/sqldeveloper" as found here: https://stackoverflow.com/questions/152837/how-to-insert-a-string-which-contains-an. You should tag your question, if possible, with the correct RDMS-tag (SQL-SERVER, ORACLE, MySQL, Postgres, or ...) anyway. – Carsten Massmann Jul 28 '17 at 13:50

1 Answers1

0

Basically what that does is take a variable that has been defined elsewhere and substitutes it into the statement. Assuming you are using Oracle, you can get a user input up front by using

ACCEPT Region PROMPT 'Enter region to be searched: '

or you can use a define statement to hard code it. Either way, once that variable is defined you can then use it by typing &Region. If someone types in "South" for example, then the compiler would see this as equivalent to

Where substr(hou.name,1,2) like 'South%'

And would thus give back results like South Carolina, South Dakota, etc.

SandPiper
  • 2,816
  • 5
  • 30
  • 52