like and not like 3

[gatetec]

I have a long list of the info with two fields below in a table.

What I want is to extract all of the rows EXCEPT *******s*. For instance, hjwbgl242s2, hgbc469s1, p1109045s1, etc are to be removed. The format of those specific rows are ******** and letter ‘s’ and followed by a number. The number of characters right before ‘s’ can vary, but once it hits ‘s’, then there is always one integer followed by.

Queue IP
hjwbgl242p2 10.6.90.174
hjwbgl242s2 10.6.90.174
hgbc469p1 10.3.4.50
hgbc469s1 10.3.4.50
p1109045p1 10.40.112.41
p1109045s1 10.40.112.41
lb22pedser 10.6.91.53
lb32c8a 10.3.8.254
lb38s11d 10.2.11.122
lb14n12w 10.1.12.46
lb64s5c 10.2.5.31
lb73w5a 10.38.80.5
……..

SELECT Printer0727.Queue, Printer0727.IP
FROM Printer0727
WHERE (((Printer0727.IP) ….

What would be the syntax on where clause?

thx much

 

[AlexCuse]

This ought to do it:

where mid(Printer0727.IP, len(Printer0727.IP) - 2, 1) <> 2

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[VRoscioli]

Certainly you mean...

where mid(Printer0727.IP, len(Printer0727.IP) - 2, 1) <> "s"

Yes?


-V

 

[PHV]

A pure SQL way:
SELECT Queue, IP
FROM Printer0727
WHERE Queue Not Like '*s?'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gatetec]

thx so much

 

[AlexCuse]

good eye, V ;-)

Ignorance of certain subjects is a great part of wisdom