SQL LIKE ANd NOT LIKE

1

Hi All, I am wondering if someone could help me with an SQL query. I need to return results from a table with words which have 'an' in them; so I am using '%an%' but i don't want the word 'AND' to show up which it is doing. How can I have one but leave out 'and'? Thanks!

2

tell it i dont want AND but everything else

where word not like '%and%' and word like %an%'
hope this helps :slight_smile:

issue of upper case and lower case might be a problem
tell me
I will give solution for that also
:slight_smile:

1 Like
3

If you are specifically looking for the word 'an' - then just put a space before and after the wildcards:

SELECT ...
  FROM ...
 WHERE column LIKE '% an %'
1 Like
4

Be careful, presumably you would want strings with "hand" and "Andy", as examples, to appear in your results, so probably do this:

WHERE column_name LIKE '%an%' AND 
    '.' + column_name + '.' NOT LIKE '%[^a-z]and[^a-z]%'
1 Like
5

Scott,

I'm curious as to what the . before an after the column_name is for ('.' + column_name + '.')

6 
use sqlteam
go

create table #andthebeatgoeson(zippididi nvarchar(150))

insert into #andthebeatgoeson
select 'Darth Vader and Luke an unsual father and son duo' union
select 'Darth Vader and Andy not sure how they are related' union
select 'Darth Vader and Handy Dandy Hand'  union
select 'Darth Vader and Handy Dandy Hand, I would be nervous with Vader propensity of chopping hands off' union
select 'Han Solow' union
select 'egghanandcheese'

SELECT 'jeffw8713', *
  FROM #andthebeatgoeson
 WHERE zippididi LIKE '% an %'
 
SELECT 'ScottPletcher', *
  FROM #andthebeatgoeson
WHERE zippididi LIKE '%an%' AND 
    '.' + zippididi + '.' NOT LIKE '%[^a-z]and[^a-z]%'

SELECT 'harish ', *
  FROM #andthebeatgoeson
where zippididi not like '%and%' and zippididi like '%an%'

drop table #andthebeatgoeson
1 Like
7

Thanks you all for writing back to me! I really appreciate it.
I actually do want it to return strings to me with 'an' e.g hands but i don't want it to return the word 'and'
image

8

As my comments indicated, it's so the the word hand and the word Andy aren't excluded from the search. If you look for just not '%and%', you would exclude them.

;WITH test_data AS ( 
    SELECT * FROM ( VALUES (1, 'Andy'), (2, 'hand'), (3, 'Jack and Jill') ) 
    AS data(string#, string)
)
SELECT *
FROM test_data
WHERE string LIKE '%an%' AND 
    '.' + string + '.' NOT LIKE '%[^a-z]and[^a-z]%'
9

Does this work with below sample data?

create table #andthebeatgoeson(zippididi nvarchar(150))

insert into #andthebeatgoeson
select 'Darth Vader and Luke an unsual father and son duo' union
select 'Darth Vader and Andy not sure how they are related' union
select 'Darth Vader and Handy Dandy Hand' union
select 'Han Solow' union
select 'Andy' union
select 'egghanandcheese' union
select '1and2'

10

Thanks! I tried that....but it is still bringing up a record with 'and' (sorry table isn't very well poulated yet but basically i only want it to return Harper Collans...
image

11

Thank you for writing - I am very new to SQL. I have a table with some information in it, its for an assignment and the question is 'Find the names of each publisher containing the letters 'an'. (This query should not return any words containing the letters 'and' in the middle of words.)
image

12

Works fine for me, lists only the (deliberately misspelled) "Harper Collans":

;WITH test_data AS (
    SELECT * FROM ( VALUES('Harper Collans'),('Simon and Schuster') ) data(PUBLISHER_NAME)
)
SELECT *
FROM test_data
WHERE PUBLISHER_NAME LIKE '%an%' AND 
    '.' + PUBLISHER_NAME + '.' NOT LIKE '%[^a-z]and[^a-z]%'
13

Unfortunately it is still returning the 2 records for me :frowning:
I will play around some more with it as maybe its something silly i am doing. Thank you so much for helping!

14

are you using Microsoft SQL Server, if so what version? If not what are you using?

15

I am using MYSQL workbench

16

MYSQL workbench is a visual tool. But what is the database you are querying? MySQL or Microsoft SQL Server?

17

Sorry MYSQL