1

I have a SQL query that I use for a search function (shown below) 

SELECT t.op_id, t.op_desc FROM operator
WHERE UPPER(t.op_id) LIKE UPPER(?);

when my web page first loads, it simply returns every operator in the table.

There a few entries in the database for which the operator id (t.op_id) contains some white space "Operator A". Is there any way that I can alter this query so that results that contain white space in the operator name are excluded?

TangoKilo
  • 1,785
  • 3
  • 25
  • 43

4 Answers4

4

To ignore white spaces, you can try

WHERE NOT LIKE '% %'

This will perform the same as INSTR.

Community
  • 1
  • 1
Kermit
  • 33,827
  • 13
  • 85
  • 121
1

You can try this :

SELECT t.op_id, t.op_desc FROM operator
WHERE t_op_id not like '% %'
Laurent S.
  • 6,816
  • 2
  • 28
  • 40
1

You could make use of the trim function. I'm assuming your operator name column is op_name. Please try the following: 

SELECT t.op_id, t.op_desc FROM operator
WHERE UPPER(t.op_id) LIKE UPPER(?) and instr(trim(t.op_name), ' ') <= 0;
vee
  • 38,255
  • 7
  • 74
  • 78
  • @Randy, yes thanks for correcting. I've updated my answer to use `instr` as you pointed out in your answer :) – vee Jul 03 '13 at 14:56
1

you might also try:

where INSTR(t_op_id,' ') <= 0
Randy
  • 16,480
  • 1
  • 37
  • 55
  • 1
    You probably wanted the other way around: `where INSTR(t_op_id,' ') <= 0` - the OP wants to exclude ones with whitespaces. – Aleks G Jul 03 '13 at 14:49