Board index » delphi » Q - SQL / D2 / Paradox Tables / SQL Syntax question!!

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!)

 

Re: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 *****

Allen <tec...@cargo.iol.ie> wrote in article
<MPG.edaf55e2cf5b5a4989...@news.indigo.ie>...

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!)

Re: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)

Other Threads