sql server partial string search

Question

I am very sure someone already that question, but I just don't know how to put in the writing.

on my SQL server database, I am trying to search the records where field1 contains abc but not abc[de]

therefore the following records will be found

'123abc222'

'abc'

The following the records will not be found

'123abc[de]'

I know

'abc123abc[de]' is tricky, but I don't have to worry about right now.

A lot people will ask why I want to perform this search instead of change the program, database, etc. That is the problem. The problem the program is in stable condition. Adding an extra field is almost mission impossible. The last thing our team wants to introduce more problem. Yes, adding the extra column or modifying database design is better, but, like most real world application, we just want to minimum change to the existing application

Thanks

SqlZim · Answer 1 · 2017-02-22T20:13:08.003

2

select * 
from t 
where field1 like '%abc%'
  and field1 not like '%abcde%'

test setup: http://rextester.com/ZZTZ72327

create table t (
    id int identity(1,1)
  , field1 varchar(32) not null
);
insert into t values
 ('123abc222')
,('123abcde');

query:

select * 
from t 
where field1 like '%abc%'
  and field1 not like '%abcde%'

results:

+----+-----------+
| id |  field1   |
+----+-----------+
|  1 | 123abc222 |
+----+-----------+

edited Feb 22 '17 at 20:13

answered Feb 22 '17 at 20:05

SqlZim

37,248
6
41
59

@user12345 Do you have additional criteria in your `where` that use `or` that aren't properly wrapped in parenthesis ? – SqlZim Feb 22 '17 at 20:09
That is what I am trying to debate. Worst comes to comes to worst, I just have to add one more column and one more control on asp.net. That is my last last option – user12345 Feb 22 '17 at 20:11
@user12345 added a rextester test to show that the query retrieves the correct results. – SqlZim Feb 22 '17 at 20:13
my apology. I did not want to use the real data. I use p. It turns out [ is an escape character. I could have use other character. I happen to use that one. Sorry to everyone. I will up-vote the correct answers. I just modified my question with [. this is the link for escape character. http://stackoverflow.com/questions/5139770/escape-character-in-sql-server. Thanks for everyone's help – user12345 Feb 22 '17 at 20:47

score 2 · Answer 2 · answered Feb 22 '17 at 20:05

2

Use like and not like:

where col like '%abc%' and col not like '%abcde%'

answered Feb 22 '17 at 20:05

Gordon Linoff

1,242,037
58
646
786

jce · Answer 3 · 2017-02-22T20:14:57.083

1

Something like:

select * from table where field like 'abc%' and field not like '%def%';

edited Feb 22 '17 at 20:14

answered Feb 22 '17 at 20:06

jce

133
1
10

it retrieves nothing – user12345 Feb 22 '17 at 20:09

score 1 · Answer 4 · edited Feb 22 '17 at 20:54

1

Thank you for everyone's answer. It is my mistake that I did not emulate the problem correctly

Quoting from the jce and SqlZim

select * 
from t 
where field1 like '%abc%'
  and field1 not like '%\[abcde]%' ESCAPE '\'

Appreciate everyone's help

edited Feb 22 '17 at 20:54

SqlZim

37,248
6
41
59

answered Feb 22 '17 at 20:49

user12345

181
1
2
18

score 0 · Answer 5 · answered Feb 22 '17 at 20:21

How about replacing all numbers with an empty character then just comparing the result using '=' comparator.

SELECT *
FROM table
WHERE field = REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
      REPLACE(@str, '0', ''),'1', ''),'2', ''),
      '3', ''),'4', ''),'5', ''),'6', ''),'7',
      ''),'8', ''),'9', '')

This will leave you out the alpha characters only.

sql server partial string search

5 Answers5