LIKE NOT '%. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

LIKE NOT '%.

Author

Topic

kmm10667
Starting Member

4 Posts

Posted - 2007-05-15 : 16:57:14

So Im working on something and I am trying to remove anything ending with .G2 and .G3 For some reason

Select name
FROM table
WHERE name LIKE '%.G1'; works but

SELECT name
FROM table
WHERE name NOT LIKE '%.G1' not only removes the .G1 like I want but EVERYTHING with a period in its name
Any help or insight why this occurs

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-16 : 00:44:26

I don't see how it removes .G1 along with everything?

Please post some sample data and expected output.

-- prepare sample data
declare @t table
(
	a varchar(20)
)

insert @t
select 'aa.G1' union all
select 'aag.G2' union all
select 'aaf.G3' union all
select 'sssaa.edh' union all
select 'aa646.dd' 

-- actual output
select * from @t
where a not like '%.G1'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kmm10667
Starting Member

4 Posts

Posted - 2007-05-16 : 16:56:26

three filed Varchar(40) equip, DATETIME plannedStartTime, DATETIME plannedEndTime

insert Into table
VALUES(ValvesA, 12/05/2007 12:00:00);
also added were
(Valves G, Valves .A1 Valves .G1) (times dont matter)

SELECT equip
FROM table
WHERE equip NOT LIKE (*.G*)
(here on I know works it was the code before I had to adjust it)
AND ( DateDiff("d",PlannedStartTime,(time taken from a form)) =0
OR (DateDiff("d",PlannedStartTime,(time taken from a form)>0 AND (DateDiff("d",PlannedEndTime,(time taken from a form))<0 )

I tested this on my web server and it work taking only valvues.G1 out ,but on my Access file it took that AND Valves .A1 .I don't know why. If anyones had a similar problem help would be MUCH appreciated.I'm pulling my hair out trying to fix this and I can't see why it isn't working.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 17:02:06

In MS Access, use * instead of % as wildcard character.

Peter Larsson
Helsingborg, Sweden

kmm10667
Starting Member

4 Posts

Posted - 2007-05-17 : 19:29:41

Tried that and Changed a few things. I've now tried the MINUS , and NOT , NOT LIKE and changeing order. The program now doesn't take out what I want and takes out random eqiupment too So I've brought it up with the first creater and hope it gets settled but thanks for pointing that out to me.

kmm10667
Starting Member

4 Posts

Posted - 2007-05-19 : 19:48:44

Just figured Id drop a lone saying I had some help and it was figured out still don;t know why the thing was messing up but it was side stepped Thank you for all the help I appreciated it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources