SQL where only part of string is selected

Hi,

I would like to create a SQL where I only select the unique first character of the field CompanyName.

So the SQL should be adjusted a little bit.
Select Distinct CompanyName from Companies

Result should be
A
G
H  etc.

The second thing I have to do is checking if a caption of a button is within the query-result and if so enable it.

procedure TFBedrijven.FilterknoppenBijwerken;
var i : Integer;
begin
  for i := 0 to ComponentCount-1 do
    if (Components[i] is TdxBarButton) then
     TdxBarButton(Components[i]).Enabled := TdxBarButton(Components[i]).Caption  >>   IN Queryresult(A,G,H);
end;

Thank you for your help.
Stef
Stef MerlijnDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kretzschmarCommented:
this depends on your database u use

for oracle for example

select distinct upper(substr(companyname,1,1)) from companies

so what database do u use?

for the second part you may  think about an array or an tobjectlist,
which can adressed with the ord(value) like


  Buttons : array[0..25] of TdxBarButton;
  ...
  //init array
  Buttons[0] := dxBarButtonA
  Buttons[1] := dxBarButtonB
  Buttons[2] := dxBarButtonC
  ....
  Buttons[25] := dxBarButtonZ

  for your evaluation first diable all Buttons

  for i := 0 to 25 do
    Bottons[i].enabled := false;
 
  then iterate through the your result

  while not dataset.eof do
    Buttons[ord(dataset.fields[0].asstring[1]) - 64].enabled := true

//not sure about the 64 offset

just from head

meikl ;-)
0
Stef MerlijnDeveloperAuthor Commented:
I'm using an MS Access database.
0
Stef MerlijnDeveloperAuthor Commented:
Found one..
SELECT Distinct MID(Bedrijven.Bedrijfsnaam,1,1) FROM Bedrijven Order by 1;
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

kretzschmarCommented:
hmm, guess acces has similar function, not in mind yet

about the array,
you could declare the array also like

Buttons : array[ord('A')..ord('Z')] of TdxBarButton;

disable would the be

for i := ord('A') to ord('Z') do
    Bottons[i].enabled := false;

iteration then

  while not dataset.eof do
    Buttons[ord(dataset.fields[0].asstring[1])].enabled := true;

meikl ;-)
0
kretzschmarCommented:
oops, you found one :-))
not seen your comment before
0
Stef MerlijnDeveloperAuthor Commented:
Where do I place the init array?

  Buttons[0] := mbA
  Buttons[1] := mbB
  etc.
0
Stef MerlijnDeveloperAuthor Commented:
One more question:
I also have a button with 0 (zero) which is used for all numeric values.
Ho to add that to the array etc.?
0
kretzschmarCommented:
>Where do I place the init array?
best in the oncreate-event of your form,
this must be done only once

>also have a button with 0 (zero)
bad, in this case an array is useless or we have some empty slots

chars 0-9 are ords 48-57
chars A-Z are ords 65-90

seven slots would be unused (58-64) chars :;<=>?@

i think about that

meikl ;-)
0
Stef MerlijnDeveloperAuthor Commented:
This code is looping forever. But we are getting there.
  while not dataset.eof do
    Buttons[ord(dataset.fields[0].asstring[1]) - 64].enabled := true
0
kretzschmarCommented:
>This code is looping forever
oops, yes, sorry

while not dataset.eof do
begin
   Buttons[ord(dataset.fields[0].asstring[1]) - 65].enabled := true; //offset should be 65
   dataset.next;  //missed this
end;

meikl ;-)
0
Stef MerlijnDeveloperAuthor Commented:
So only the part with the 0 (zero) value remains.
The rest is working perfectly. Thanks a lot.

Stef
0
kretzschmarCommented:
well, if there only the char 0, then we could expand the array by one slot

//init array
  Buttons[0] := dxBarButtonA
  Buttons[1] := dxBarButtonB
  Buttons[2] := dxBarButtonC
  ....
  Buttons[25] := dxBarButtonZ
  Buttons[26] := dxBarButton0

//iteration
while not dataset.eof do
begin
   if dataset.fields[0].asstring[1] = '0' then
     Buttons[26].enabled := true
   else
     Buttons[ord(dataset.fields[0].asstring[1]) - 65].enabled := true; //offset should be 65
   dataset.next;  //missed this
end;

meikl ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stef MerlijnDeveloperAuthor Commented:
Yes that seems to be the solution if not the 0 (zero) should be set to True when any numeric value is the first character.
So 0,1,2,3,4,5,6,7,8 and 9.
0
kretzschmarCommented:
>0 (zero) should be set to True when any numeric value is the first character.
should be not so hard

the change:

//iteration
while not dataset.eof do
begin
   if dataset.fields[0].asstring[1] in ['0'..'9'] then  //change here
     Buttons[26].enabled := true
   else
     Buttons[ord(dataset.fields[0].asstring[1]) - 65].enabled := true; //offset should be 65
   dataset.next;  //missed this
end;

meikl ;-)
0
Stef MerlijnDeveloperAuthor Commented:
Well Meikl, you earned your point with ease.
Thank you.
Stef
0
kretzschmarCommented:
glad you got it work :-))

good luck again

meikl ;-)
0
Stef MerlijnDeveloperAuthor Commented:
Just some small adjustment:
The offset must be 64 after all.

  // Deze procedure zet de verschillende filterknoppen aan waarvoor
  // daadwerkelijk gegevens beschikbaar zijn.
  With DM.QAlgemeenGebruik do
  begin
    Close;
    SQL.Clear;
    SQL.Add(' SELECT Distinct MID(Bedrijven.Bedrijfsnaam,1,1) AS BeginLetter FROM Bedrijven Order by 1 ');
    Open;
  end;
  for i := 0 to 26 do
    MyButton[i].enabled := false;
  DM.QAlgemeenGebruik.First;
  while not DM.QAlgemeenGebruik.Eof do
  begin
     if DM.QAlgemeenGebruik.Fields[0].asstring[1] in ['0'..'9'] then  //change here
       MyButton[0].enabled := true
     else
       MyButton[Ord(DM.QAlgemeenGebruik.Fields[0].asstring[1]) - 64].enabled := true; //offset should be 64
     DM.QAlgemeenGebruik.Next;  //missed this
  end;
0
kretzschmarCommented:
>The offset must be 64 after all.

oops, my mistake, sorry :-))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.