CODEWITHSUNDEEP
sql
Mayank Negi
Mayank Negi

Reputation: 13

Query to retrieve only columns which have last name starting with 'K'

Here is an example of how the table looks]

The name column has both first and last name in one column.

Look at the 'rep' column. How do I filter only those rep names where the last name is starting with 'K'?

Upvotes: 1

Views: 927

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Assuming that the name is <firstname> <lastname> you can use:

where rep like '% K%'

Upvotes: 1

Berin Loritsch
Berin Loritsch

Reputation: 11463

The way that table is defined won't allow you to do that query reliably--particularly if you have international names in the table. Not all names come with the given name first and the family name second. In Asia, for example, it is quite common to write names in the opposite order.

That said, assuming you have all western names, it is possible to get the information you need--but your indexes won't be able to help you. It will be slower than if your data had been broken out properly.

SELECT rep,
  RTRIM(LEFT(LTRIM(RIGHT(rep, LEN(rep) - CHARINDEX(' ', rep))), CHARINDEX(' ', LTRIM(RIGHT(rep, LEN(rep) - CHARINDEX(' ', rep)))) - 1)) as family_name
WHERE family_name LIKE 'K%'

So what's going on in that query is some string manipulation. The dialect up there is SQL Server, so you'll have to refer to your vendor's string manipulation function. This picks the second word, and assumes the family name is the second word.

  • LEFT(str, num) takes the number of characters calculated from the left of the string
  • RIGHT(str, num) takes the number of characters calculated from the right of the string
  • CHARINDEX(char, str) finds the first index of a character

So you are getting the RIGHT side of the string where the count is the length of the string minus the first instance of a space character. Then we are getting the LEFT side of the remaining string the same way. Essentially if you had a name with 3 parts, this will always pick the second one.

You could probably do this with SUBSTRING(str, start, end), but you do need to calculate where that is precisely, using only the string itself.

Hopefully you can see where there are all kinds of edge cases where this could fail:

  • There are a couple records with a middle name
  • The family name is recorded first
  • Some records have a title (Mr., Lord, Dr.)

It would be better if you could separate the name into different columns and then the query would be trivial--and you have the benefit of your indexes as well.

Your other option is to create a stored procedure, and do the calculations a bit more precisely and in a way that is easier to read.

Upvotes: 2

Related Questions