• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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
0
Stef Merlijn
Asked:
Stef Merlijn
  • 9
  • 9
4 Solutions
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now