Q - SQL / D2 / Paradox Tables / SQL Syntax question!! 
Author Message
 Q - SQL / D2 / Paradox Tables / SQL Syntax question!!

Hi,

I have a table that stores client details. There is a company policy that
the names etc must be typed in mixed upper/lower case (ie: "Joe Public,
Mary Bloggs, etc").

I want to query the table without regard to case. I have tried the
following without success...

Select FirstName, LastName, Address from Clients where Upper(FirstName) =
:XFirstName

Then I pass the ParamByName as UpperCase(SearchFirstName.Text)

I would have thought that this (a) Converted the Search text into upper-
case, and when running the query compared the two strings using
uppercase, but to no avail. I then thought that it was due to the fact
that Paradox most likely "pads" with spaces to the size of the field, so
I tried to "Trim" the select as follows...

Select FirstName, LastName, Address from Clients where
Trim(Upper(FirstName)) = :XFirstName

But still nothing!

Appreciate any assistance by email.

Chio!

- Allen.

--
(To reply remove the NO from my address!)



Wed, 18 Jun 1902 08:00:00 GMT  
 Q - SQL / D2 / Paradox Tables / SQL Syntax question!!

***** Begin of mail *****

Try to include ' ' or " " in your parameter before bind into you SQL
statement, for example:

UpperCase( ''' & SearchFirstName.Text & ''')

select FirstName, LastName, Address from Clients where Upper(FirstName) =
:XFirstName

then your select statement will be as follow:

select FirstName, LastName, Address from Clients where Upper(FirstName) =
"Joe Public"

if SearchFirstName.Text = Joe Public.

The trim function you use will only trim the trailing and leading "white
space" but not the "white space" in the middle of the string.

To troubleshoot your codes, try :

Method 1: select a record from your database using SQL and compare it in
your coding.
Method 2: Hard-code the FirstName in your SQL, test it whether it will
return a record or not
Method 3: Display your SQL after bind with your parameter, check the SQL
statement whether is OK.

***** End of mail *****



Quote:
> Hi,

> I have a table that stores client details. There is a company policy that
> the names etc must be typed in mixed upper/lower case (ie: "Joe Public,
> Mary Bloggs, etc").

> I want to query the table without regard to case. I have tried the
> following without success...

> Select FirstName, LastName, Address from Clients where Upper(FirstName) =
> :XFirstName

> Then I pass the ParamByName as UpperCase(SearchFirstName.Text)

> I would have thought that this (a) Converted the Search text into upper-
> case, and when running the query compared the two strings using
> uppercase, but to no avail. I then thought that it was due to the fact
> that Paradox most likely "pads" with spaces to the size of the field, so
> I tried to "Trim" the select as follows...

> Select FirstName, LastName, Address from Clients where
> Trim(Upper(FirstName)) = :XFirstName

> But still nothing!

> Appreciate any assistance by email.

> Chio!

> - Allen.

> --
> (To reply remove the NO from my address!)



Wed, 18 Jun 1902 08:00:00 GMT  
 Q - SQL / D2 / Paradox Tables / SQL Syntax question!!

Try using AnsiUpperCase instead of UpperCase

mrmf

Quote:
> Select FirstName, LastName, Address from Clients where Upper(FirstName) =
> :XFirstName

> Then I pass the ParamByName as UpperCase(SearchFirstName.Text)



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. SQL sent from D2.01 to Oracle via SQL*Net D2.01

2. Delphi with Paradox, SQL Syntax Document wanted

3. Simple SQL syntax question

4. Newbie question: SQL syntax

5. SQL Syntax Help with 3 Table Query

6. SQL JOIN syntax with > 2 tables

7. Space in table name : SQL syntax?

8. SQL AnyWhere and D2 questions problems

9. D2:Views in Local SQL (Paradox) ?

10. Converting VB app to D2, uses SQL 6.0 and SQL 6.5

11. D2: Query.Refresh not working for SQL tables?????

12. joining a paradox table to an MSAccess table in an SQL query

 

 
Powered by phpBB® Forum Software