12

I have a SQL query SELECT * FROM table WHERE column LIKE '%pdd%'.

The problem is I need to get all results excluding those which start with "pdd", by which I mean find everything where "pdd" is not at the beginning. How could it be done?

I do need to match "pdd" when it is not at the beginning of the column.

Air
  • 8,274
  • 2
  • 53
  • 88
Anthony
  • 681
  • 3
  • 9
  • 20

4 Answers4

22

I assume you mean all rows that match "pdd" except those where "pdd" is at the beginning.

SELECT * FROM table WHERE column LIKE '_%pdd%'.

The "_" wildcard in LIKE predicates means "one of any character," equivalent to "." in regular expressions.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Sorry but no. this will NOT work in cases like `pdd hello pdd` because the very first p matches the `_` and the rest of the string (including another `"pdd"`) will match `%pdd%` . the OP never mentioned that the "pdd" string will always be unique among the string, so multiple ocurrences of it, where one of them is at the begining will make this solution not to work. OP, consider unaccepting this answer (as it is incorrect) and accepting the ones by quosoo or Marc. – DiegoDD Jan 27 '14 at 23:35
  • @DiegoDD, That's a good point, I overlooked the possibility of the pattern occurring twice. This is an old question, I'll be interested to hear from the OP on this matter. But since he accepted it, I'll assume it worked for the cases he needed. – Bill Karwin Jan 28 '14 at 01:40
  • I agree on the fact that the OP my have found it useful and had no problem, but since this is a community site, you could edit the answer to state that it woulnd't work on such case, since other people are likely to find the answer (just as I did), and being an old question doesn't make it less useful or valid. Thanks for the reply. – DiegoDD Jan 28 '14 at 15:47
5

If I understand your requirement correctly what you need is: 

SELECT * FROM table WHERE column LIKE '%pdd_%' and column NOT LIKE 'pdd_%'
quosoo
  • 829
  • 4
  • 10
4
SELECT * FROM table WHERE column LIKE '%pdd%' AND column NOT LIKE 'pdd%'

You can optimise the query depending on how frequent these occurrences are in your table.

Marc
  • 351
  • 4
  • 13
0

Try:

SELECT * FROM table WHERE column NOT LIKE 'pdd%'
tpow
  • 7,600
  • 11
  • 59
  • 84