question

vpn005 avatar image
vpn005 asked

How to find all contacts starts with a letter in their last name in sql,

Example( jhon, bosco) in contact’s table , jhon is first and bosco is last name, how to find all contacts starts with a letter(eg:- b) in their last name,

Note. Fists name and last name in a single colum separated by comma

sql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So, which name comes before the comma? The first name or the last name? I'm asking because it seriously matters for an answer.

1 Like 1 ·

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

Not sure if this is homework or not.

Anyway, what you need to do is use the SUBSTRING function to look at one character following the pattern ', ' (comma and space). You will find the position of that pattern with the function CHARINDEX.

So basically WHERE SUBSTRING ( column_name, <your charindex expression>, 1 ) = 'b'

A warning though: If this is a big table, you will get bad performance. There's no way for SQL Server to search in an index for this expression, so it will mean (at least) an index scan, possibly a clustered index scan (table scan).

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.