Search code examples
sqlsql-like

Like with & in SQL Script

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?

Solution

  • 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.