SQL Firebird search for last names by initial letter

Asked

Viewed 283 times

0

Precise research by the letter initial of the surnames within a column that has full and compound names.

Examples:

Jean Carlos

Luciana Menezes Rezende

Roberto Menezes Souza

Vero Vinicius

Desired result when searching for last names with the letter M:

Menezes Rezende

Menezes Souza

The closest I could get was:

Select

serv.nome as Nome,

substring (serv.nome from position 
(' ' in serv.nome) for character_length(serv.nome)) as Sobrenome
from fol_servidor serv
where  serv.nome like '%' || :NOME;
sql firebird

  • It should not be: Where Last name like '%' || :FIRST NAME; ?

  • I am using ibexpert for handling and when I put " Where Last Name like '%' || :FIRST NAME; " error of Column Unknown. LAST NAME. At line 7, column 17.

  • I don’t work with Firebird but you can always do: Where substring(Serv.nome from position (' in Serv.nome) for character_length(Serv.nome)) like '%' || :NOME; or take only the first character of the surname and test if it equals :NOME.

  • I tried that way but it does not return the desired results as I quoted from example in the question

  • In the question the example is different from my suggestion.

  • I refer to the results I quoted... The way I said does not return the desired results

  • 1

    There really is an error the correct is: like :NAME || '%';.

Show 2 more comments

2 answers

2


You’re already in the way, you SUBSTRING almost meets, missed to put it to perform.

Ex:

SELECT C.NOME,
       SUBSTRING(C.NOME FROM POSITION(' ', C.NOME) + 1 FOR CHAR_LENGTH(C.NOME))   
  FROM CLIENTES C
 WHERE SUBSTRING(C.NOME FROM POSITION(' ', C.NOME) + 1 FOR CHAR_LENGTH(C.NOME)) LIKE :NOME_PESQUISA || '%'

Basically we’re looking for anything that starts with the search source, but after the first space in the name.

Note problems if there is no space...

Edit1.

You can also use C.NOME CONTAINING(' ' || :NOME_PESQUISA) which is better/smaller than the SUBSTRING...

  • This is exactly what I sought thanks for the time provided and the tips was of great help !

0

Good afternoon, from what I understand you want all the names started by x letter

select sobrenome
from table
where sobrenome like 'X%';

Where X represents the first letter of the name and then "%".

Good luck

  • Not exactly. I need to search the LAST NAME by the initial letter

  • because creating another column on the table would be simpler and your protection would become more dynamic

  • In case I’m trying to learn more about sql and Firebird I already caught a database that this way

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.