select * from table_name where column like ' '

Question

I need to find records containing html code such as '&nbsp' But when I try to run the select * from table_name where column like '&nbsp%' I got prompt asking for the value of nbsp. I guess the database thinks that nbsp is a parameter. I am wondering if the is an escape character so that I can tell the database that "&" is part of my query string. I tryed '\&nbsp' but didn't work.

My environment is Oracle 9i with sqlplus client.

Thanks.

score 11 · Accepted Answer · answered Dec 24 '09 at 08:50

11

Have a look at this:

SQL Plus FAQ

e.g.

SET ESCAPE '\'
SELECT '\&abc' FROM dual;

answered Dec 24 '09 at 08:50

davek

22,499
9
75
95

score 11 · Answer 2 · answered Dec 24 '09 at 08:55

11

Easier way:

SET DEFINE OFF

See: SET DEFINE

answered Dec 24 '09 at 08:55

wadesworld

13,535
14
60
93

This works very well too. But I saw another answer first, so I had to mark the other one as answer. But one up vote for you :) – Wei Ma Dec 25 '09 at 02:20

score 2 · Answer 3 · answered Dec 24 '09 at 08:50

2

The backslash should work, but I think you need to start your query with

SET ESCAPE ON

answered Dec 24 '09 at 08:50

Paddyslacker

1,880
1
14
20

Duplicate answer to the other one, but saw it was only 5 mins different from the one that's been upvoted more, and this one was actually first. That's rough voting... here's my +1 – vapcguy Jul 22 '16 at 17:13

score 0 · Answer 4 · answered Jun 27 '11 at 17:22

0

In PL/SQL, you would use:

BEGIN select <Column> from <Table_name> into <Variable> where <Column> LIKE '\&nbsp\%' ESCAPE '\'; END /

Resources:

Wilcards in SQL on PSOUG.org

LIKE Condition in Oracle® Database SQL Language Reference 11g Release 2 (11.2)

answered Jun 27 '11 at 17:22

philippeko

501
5
16

select * from table_name where column like ' '

4 Answers4

Linked

Related