UPPER, SQL Statement didnt work

You are not comparing UPPPER to uppper.... you should have done this:

SELECT *
FROM text
WHERE
(UPPER(text.descripcio) LIKE '%GO%')

Good luck!!

first this select

SELECT *
FROM text
WHERE
(UPPER(text.descripcio) LIKE '%Go%')

will allways return nothing

better

SELECT *
FROM text
WHERE
(UPPER(text.descripcio) LIKE '%GO%')

about your typemismatch, i wlll look tomorrow,
to tired for now

meikl ;-)

hi DrDelphi :-))

Look at this example:

procedure TForm1.Button1Click(Sender: TObject);
begin
Query1.Close;
Query1.Sql.Clear;
Query1.SQl.Add('Select * from Clientes ');
Query1.Sql.Add('Where  Upper(Provincia) Like'+''''+'%'+ Edit1.Text +'%'+'''');//to show the Sql delete
ShowMessage(Query1.Sql.text);
Query1.Open;
end;


Best Regards.
Marcos.

I am going to test your example, Marcos... Yes I know thats return nothing, its an easy exmple... but the problem is the typemismatch error, I dont know why, but "upper" didnt work in my machine...

what database are you using? perhaps UPPER is not a supported function in your database?

.db is paradox 7 or 8, in a memo field (text.descripcio) and Delphi 6.

>in a memo field

you can't search in a memo-field,
that is not possible via SQL

Yes, this is the problem... I cant use upper in memo-field...

Always I used local tables .dbf Dbase V. I had many problems from corrupt files up to files with more than 500mb in a .dbf with 10000 records.. Then I changed to .db paradox 7, much much better, performance, size.

But now I discover that i cannot compare case insensitive, there are anyway to have the same result as database "MYSQL" (can compare case insensitive and puntuaction dont matter) with local tables to use in my program.

Thanks.

the problem is,
that your text is in a memofield=blobfield
and there is no database, which can do a compare in a blobfield->mySQL may have the same problems, also oracle. how long is your memo-field.
longer than 255 chars?

How do, kretzschmar ?

As for the question at hand... unfortunately, it is true that you will not be able (in SQL) to find an occurence of a given string (upper or lower case) from a blob (mmemo) field. The only alternative that I am aware of is to stream that field out and do a POS on it. Ugly. It is possible to change your blob to a VARCHAR (some really large number)?


Good luck!!
 

I dont know is more than 255, but its a format text. I think that some record has more than 255 char.

I told about mysql because I convert my dbf memo-field with some program (a program to work with mysql), and work great using php+mysql (mysql server) and I dont have the problem of case sensitive.

The pos in every memo field when i have to compare in 2 memo field in every record, and totally of 30000 records, is not the way of performance I think.

And the last i like, iam going to test... perhaps VARCHAR(3000) for 3.000 characters will be enough... but format text how i do that..

Thx,

The varchar(some really large number) didnt work...

create table index ( code varchar(9),
               descripcio varchar(3000),
               observacio varchar(3000)
                  )
some error ocurr...

For some reason the sql (varchar) is the same as "ftString" in field type, you cannot exceded the 255 limit.

This work:

create table index ( code varchar(9),
               descripcio varchar(254),
               observacio varchar(254)
                  )
But its not what i want...

Anybody know about widestring (field type in delphi,paradox), because when i want to convert the memo-field to another table with widestring field, its only put the first letter.

I use

table1.first;

while not(table1.eof) do
begin

append;
table1.fieldvalues['DESCRIPCIO']:=table2.fieldvalues['DESCRIPCIO'];
 table1.fieldvalues['OBSERVACIO']:=table2.fieldvalues['OBSERVACIO'];
post:

end;


Thks

perhaps you can try

Table1.Fields[0].AsString := Table2.Fields[0].AsString;

fisher2k4:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.