Sorting

[tjc240e]

I am wanting to sort a database by characters in the last name of members. The last_name field in the database is not consistant (meaning not all caps or lower).
I have a field on my form that you can type in any parts of the last name you want to search on.

How could i sort my database on the last_name field regardless of the case of what is requested?

Does that make any sense?

 

[Zathras]

This is more of a database question than a Delphi one.

Have you tried using ORDER BY in the SQL statement? For example, SQL Server is normally set up with the default attribute for non-case-sensitive sequencing.

Alternatively, if your DBMS supports it, you can include something like UPPER(MYTABLE.LASTNAME) in the SELECT list and ORDER BY that column positionally. I.e, if that is the first thing following the SELECT, you can use ORDER BY 1.

Naturally, I am assuming you already are using something like

 + ' WHERE MYTABLE.LASTNAME LIKE ' + QuotedStr( UpperCase(Edit1.Text) + '%' )

There are other things you can do if you are using BDE, but I'm assuming you are going the preferred way with ADO.

 

[tjc240e]

Actually I was using this code:

xLNSelect := edSearch.text [i](from textbox on form)[/i];
MemberLookup.EnsureOpen;
MemberLookup.SetTag('LAST_NAME');
MemberLookup.SetFilter('LAST_NAME >= ' + QuotedStr(xLNSelect)+' .and. LAST_NAME <= ' + QuotedStr(xLNSelect+'z');
MemberLookup.GoTop;

It is not an SQL database it is a DBF. I'm not the database designer, i'm just the guy writing report interfaces from the existing databases sent to me.

 

[Zathras]

Don't know anything about the MemberLookup object, but if your DBMS is case-sensitive then perhaps you could try something like this:

MemberLookup.SetFilter('LAST_NAME >= ' + QuotedStr(UpperCase(xLNSelect))+' .and. LAST_NAME <= ' + QuotedStr(LowerCase(xLNSelect)+'z');

What is DBF? Design by Fire? dBase Format? Dannmarks Badminton Forbund? (Google wasn't too helpful here.)

 

[tjc240e]

Oh sorry MemberLookup is just the TCBTable object i have setup.

That >= Uppercase and <= LowerCase didnt work, it gave me everything from the uppercase through the end of the list.

i'm wondering if it wouldnt just be easier to create a new temp table with the last name uppercased.

 

[Zathras]

...through the end of the list...

Sorry, of course it would. I wasn't thinking clearly.

A temp table could work, but the time it would take to execute might be a problem depending on how large it is.

Perhaps you can set up your filter so that the effect would be like (e.g. for "Smith")
[tt]
((LASTNAME >= 'S' .and. LASTNAME < 'Szzz') .or. (LASTNAME >= 's' .and. LASTNAME < 'szzz')) .and. Upper(LASTNAME) LIKE 'SMITH%'
[/tt]
Or if the DBMS doesn't support "LIKE" then something like
[tt]
(LASTNAME >= 'S' .and. LASTNAME < 'Szzz') .or. (LASTNAME >= 's' .and. LASTNAME < 'szzz')
[/tt]
and then use code to select only the names you want.