UPPER, SQL Statement didnt work

fisher2k4
fisher2k4 used Ask the Experts™
on
I have this expresion

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


the delphi return this error: "type mismatch in expresion".

I want to compare case insensitive, what I am doing wrong. I have Delphi 6, and its a local table paradox.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

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

Good luck!!
Top Expert 2004

Commented:
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 ;-)
Top Expert 2004

Commented:
hi DrDelphi :-))
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

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.

Author

Commented:
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?

Author

Commented:
.db is paradox 7 or 8, in a memo field (text.descripcio) and Delphi 6.
Top Expert 2004

Commented:
>in a memo field

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

Author

Commented:
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.
Top Expert 2004

Commented:
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?

Commented:
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!!
 

Author

Commented:
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,

Author

Commented:
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;

From another board I found the solution:

(http://archive.dbforums.com/118/2002/4/355258)
-----------------------------------------
some person:

"yes it really is. I created the field this way: with FieldDefs.AddFieldDef do begin
Name:='MyField'; DataType:=ftWideString; // I tried with and without the following
line; Size:=500; end;
And Database Desktop says that the field type is alpha (not widestring) and
size is 1.
What should I do now?"

another person:

"This simply means that Paradox doesn't support widestring fields. You may experiment
with memo field for widestring storage"
------------------------------

The best way (my way) to do what i want is:

To create 2 tables.

1. memo_table
   a) memo_table.observacio (field)
   b) memo_table.descripcio (field)

2. memo_Insensensitive
  a) memo_Insensensitive.observacio
  b) memo_Insensensitive.descripcio

When i want to save a record, save in memo_table, and to do an ansiuppercase to the text that i want to save in memo_Insensitive. When i want to search, i do in the memo_insensitive table. Yes this double the space storage, but its the best way i found to do this.....

This ends my problem... An the topic i Think...

thxs to all.
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial