InStr in SQL - MS Excel Query

[barkley1979]

I am not sure if you can help me here, but I cannot find anywhere else that can help me. (If anyone knows of a forum more suited to this question I would appreciate it!)

I need to create the following in SQL so that a query I am running will pull the right data into Excel for me.

I need to basically
SELECT * WHERE WO.WOTYPE is not in WO.TASKNUM
A typical example of the data is:
WOTYPE TASKNUM
CATR RM-CATR-EQUIP
CATR RM-CHG-GENERAL

Obviously I'd only want the second line showing in my results.

Can anyone assist?
Thanks.

 

[DanChard]

Try either of the following:

select *
from wo
where wotype not like '*' & tasknum & '*';

or,

select *
from wo
where not instr(tasknum,wotype);

(not sure about the 2nd)

Good luck,
Dan

 

[DanChard]

Sorry,

I think tasknum and wotype should be the other way round in my first query...

 

[Golom]

Can I restate this as

You want all records where the value in the "WOTYPE" field is NOT a substring of the value in "TASKNUM"?

If that's correct then try

   Select * From tbl 
   Where Instr(1,TASKNUM,WOTYPE) = 0

 

[barkley1979]

Thanks for both the replies.
With:

select *
from wo
where tasknum not like '*' & wotype & '*';

I get 'Invalid operator for data type. Operator equals boolean AND, type equals varchar. Statements could not be prepared'.

For:

Select * From tbl
Where Instr(1,TASKNUM,WOTYPE) = 0

I get 'Instr is not a recognized function name.'

As I'm new to all this I haven't a clue what's going on here!

Thanks again for the time.

 

[Golom]

"Instr" is a function available in MS/Access that locates the position of one string within another. If your system doesn't know about it then, are you using Access?

Try this (adapted from DanChard's code) which is standard SQL and should work on any RDBMS.

SELECT A.* FROM tbl As A
WHERE NOT (A.TASKNUM LIKE '*' + A.WOTYPE + '*' )