How do i use the LIKE clause in BaaN sql.

For eg :
var1="123456789-123"

select ...... from ..... where <field> NOT LIKE <var1>
selectdo
endselect

I want to select data which do not start with 123456789

The database is Triton on SunSolaris

You should try:

var1 = "^[^123456789].*"

Explanation:
The first '^' defines the beginning of the string.
With '[^123456789]' you select strings with any character at this position except '123456789'.
The first character of the string can be followed by any other string (defined by '.*').

You have to change also your select-command:

select ...... from ..... where <field> LIKE <var1>
selectdo
endselect

Refer to the threads -

wildcards in SQL (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=5905)

qptool select on like or substring (http://www.baanboard.com/baanboard/showthread.php?s=&threadid=8763&highlight=performance+guide)

Most of the samples show the LIKE being used with a fixed string.

for eg : ... where <field> LIKE ".*RF.*"

What shud be done when the "RF" itself is a database column.

For eg:

select ticst001.sitm,ticst001.qucs,tisfc001.mitm
from ticst001, tisfc001
where tisfc001.pdno inrange :pdno.f and :pdno.t
and ticst001.sitm not like tisfc001.mitm
selectdo
rprt_send()
endselect

here the tisfc001.mitm = 27540458-005
ticst001.sitm cud be anything from 27540458 to 27540458-005
And i do not want the "LIKE" matching records to be SELECTED.

Thanks

-------------------------------------------------------------
and ticst001.sitm not like tisfc001.mitm
-------------------------------------------------------------
This sql phrase should be
"and ticst001.sitm <> tisfc001.mitm "


For the range phrase, if your item numbers have consistant length you could use:

"and ticst001.sitm(1;8) = tisfc001.mitm(1;8) "

(I think ticst001.sitm can never equal to tisfc001.mitm otherwise you would have a recursive bom)

Terry