Q

Adding a variable to a SQL statement

I would like to put a variable in my SQL statement, but it doesn't seem to work:

SELECT * FROM NormalClient WHERE ClientSurname LIKE '%' & strlastname & '%'

What is wrong?


It looks like you are trying to concatenate strings containing wildcard percent signs around a programming language variable. Obviously, you need the value of strlastname, not the string strlastname.

Without knowing which programming language you're using, it's difficult to guess the correct syntax, but I'll give it a try.

Let's assume that the strlastname variable contains the value McGillicuddy. What you need to do is ensure that the SQL statement ends up looking like this--

LIKE '%McGillicuddy%'  

Notice that the SQL syntax requires that the LIKE string be an actual literal string, enclosed in quote marks. Therefore, you will probably need to "escape" the opening and closing quotes of the string if you are concatenating the wildcard percent signs to the programming language variable. One way to do this is to repeat the single quote mark--

LIKE '''%' & strlastname & '%'''  

(If that's hard to read, it's quote quote quote percent quote at the front and quote percent quote quote quote at the end.)

You could also try nesting single quotes within double quotes--

LIKE "'%" & strlastname & "%'"  

(If that's hard to read, it's doublequote singlequote percent doublequote at the front and doublequote percent singlequote doublequote at the end.)

For More Information


This was first published in April 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close