Hi,

I want to select records from specific table based on some pattern in item code. Like item code containing characters RM06CH (given on form). I am writing statement like:

recs = 0
opro = shiftr$(opro)
select tcibd001.*
from tcibd001
where tcibd001._index4 inrange {:citg, :mitm.f} and {:citg, :mitm.t}
and tcibd001.item like "%" & :pattern & "%"
selectdo
recs = recs + 1
| create.routing.record()
| create.operation.record()
endselect
message("records matching pattern:" & str$(recs))

pattern is a form variable of domain tcseak (search key).
But no luck. Can anybody guide, how this can be done ?

Vishal

try
.... like ".*" & pattern & ".*"

Here's the way I did it.



| 'name' is a form input field
extern domain tcnama name


function read.main.table()
{
long sql

sql = sql.parse("select tiedm310.*, tiedm301.*, tccom001.*, tized910.* " &
"from tiedm310, tiedm301, tccom001, tized910 " &
"where tiedm310._index1 inrange {:orno.f} " &
"and {:orno.t} " &
"and tiedm310.osta between :osta.f and :osta.t " &
"and tiedm310.prcd refers to tiedm301 " &
"and tiedm310.orno refers to tized910 " &
"and tiedm310.ecom refers to tccom001 " &
"and tccom001.nama like """ & name & """ " &
"order by tiedm310._index1")
sql.exec(sql)
while not sql.fetch(sql)
rprt_send()
endwhile
sql.close(sql)
}

This is my form 2 notes for the wildcard usage in the 'name' field.



+------------------------------------------------------------------------------+
| VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV Company: VVV |
|------------------------------------------------------------------------------|
| Wildcard Characters |
| ^ beginning of line |
| $ end of line |
| . any character |
| * all or none characters matching previous character |
| \ literal use of next character |
| [] characters within brackets, ^(not), -(through) |
| |
| Examples |
| .* all patterns |
| ^A.* patterns beginning with A |
| .*Z$ patterns ending with Z |
| .*M.* patterns containing M |
| [A-E].* patterns beginning with A through E |
| [^A].* patterns not beginning with A |
| [ACE].* patterns beginning with A, C or E |
| |
| |
+------------------------------------------------------------------------------+ Hope this helps,
fallguyjg