-2

I have a SQL query like:

(u.user_contact_no LIKE " + "%" + ContactNo + "%" + " or  u.user_contact_no IS NULL" + " or u.user_contact_no = " + ")

it returns,

(u.user_contact_no %9876543210% or  u.user_contact_no IS NULL or u.user_contact_no = )

But expecting like,

(u.user_contact_no LIKE "%9876543210%"  or  u.user_contact_no IS NULL or u.user_contact_no = "")

Kindly tell me, how to rewrite my query ?

Kavitha P.
  • 155
  • 1
  • 2
  • 13
  • Which DBMS and programming language are you using? The string concatenation operator in SQL is `||` not `+` –  Jan 08 '15 at 07:47
  • @a_horse_with_no_name Orcacle uses `||` for string concatenation, SqlServer uses `+` – Hans Kesting Jan 08 '15 at 08:29
  • Where do you have this concatenation? in your sql or in some language (which?) where you build the statement to be sent to the database? – Hans Kesting Jan 08 '15 at 08:31
  • If you follow the guidelines [here](http://stackoverflow.com/help/how-to-ask) then we can provide an answer which is more relevant to your question. – G B Jan 08 '15 at 08:42
  • @HansKesting: SQL (the query language) uses `||`. This was defined in the SQL standard back in '86. And it's not only Oracle. Also Postgres, DB2, Informix, Firebird and many other DBMS adhere to that standard. And the question is tagged `sql` not `sql-server` so one can assume we are talking about "standard SQL" here. –  Jan 08 '15 at 08:49
  • written as, (u.user_contact_no LIKE '" + "%" + ContactNo + "%" + "' or u.user_contact_no IS NULL" + " or u.user_contact_no ='''' )...it works good. – Kavitha P. Jan 08 '15 at 09:21

5 Answers5

3

The ANSI/ISQ SQL standard specifies the use of double quotes for identifiers. For example when a table name includes a space character: "First Name". Or when a reserved word is used as identifier, for example column name "DAY".

Single quotes are used for literals, e.g. 'Matt'.

select "Last Name"
from "some odd table"
where "First name" = 'Matt'
jarlh
  • 42,561
  • 8
  • 45
  • 63
1

Assuming you are querying SQL Server: you need to be using the single quote character and escaping the single quotes you want to include in the query string you appear to be building.

declare @ContactNo varchar(10) = '9876543210'
declare @sql varchar(max) = 'select... from... where... and (u.user_contact_no LIKE ''%' + @ContactNo + '%'' or  ISNULL(u.user_contact_no,'''') = '''')'
select @sql

returns

select... from... where... and (u.user_contact_no LIKE '%9876543210%' or  ISNULL(u.user_contact_no,'') = '')

so to create an empty string literal within your SQL string you need to escape both single quotes so you end up with four single quote characters.

G B
  • 1,412
  • 10
  • 12
  • i tried like, (u.user_contact_no LIKE '" + "%" + ContactNo + "%" + "' or u.user_contact_no IS NULL" + " or u.user_contact_no ='''' )...it works good. – Kavitha P. Jan 08 '15 at 09:21
  • @KavithaP - If you upvote the answer or mark this as the answer or both then others can easily find what works well. – G B Jan 08 '15 at 09:33
0

It should be like this

(u.user_contact_no LIKE + "'%" + ContactNo + "%'" + or  u.user_contact_no IS NULL")

More Information about Single and Double Quote

Community
  • 1
  • 1
ashokhein
  • 1,048
  • 12
  • 38
0

You are missing Single Quotes

(u.user_contact_no LIKe "' + "%" + ContactNo + "%" + '" or  u.user_contact_no IS NULL")
Dgan
  • 10,077
  • 1
  • 29
  • 51
  • (u.user_contact_no LIKE " + "%" + ContactNo + "%" + " or u.user_contact_no IS NULL" + " or u.user_contact_no = " + ") – Kavitha P. Jan 08 '15 at 08:48
  • im getting like, (u.user_contact_no %98% or u.user_contact_no IS NULL or u.user_contact_no = ) but expecting like, (u.user_contact_no LIKE "%98%" or u.user_contact_no IS NULL or u.user_contact_no = "") – Kavitha P. Jan 08 '15 at 08:51
0

Try

(u.user_contact_no LIKe '" + "%" + ContactNo + "%" + "' or  u.user_contact_no IS NULL")
rcs
  • 6,713
  • 12
  • 53
  • 75