How to write query that will do unusual distinct select from sql table?

Question

Suppose I have some table in SQL Database with many data and some of the rows are almost identical, for example:

|===========================================|
|           Message                         |
|===========================================|
|              ...                          |
|-------------------------------------------|
|    account is rejected: <aa>              |
|-------------------------------------------|
|    account is rejected: <bb>              |
|-------------------------------------------|
|    mailbox unavailable: 550 550 <[email protected]> |
|-------------------------------------------|
|    mailbox unavailable: 550 550 <[email protected]> |
|-------------------------------------------|
|                 ...                       |

for my purposes 2 first lines are identical and 2 last lines are identical, so the query should return

1. account is rejected:
2. mailbox unavailable: 550 550

I thought to write query that will select rows, eliminate chars after '<' sign and the will do DISTINCT, but I don't know how to do all it in SELECT

Can you help me to write such query?

Answer 1

The following removes everything from the first < onwards.

The GROUP BY is more flexible than using DISTINCT but accomplishes the same thing in this case.

The CASE statement is there to cater for the possibility of messages that do not have a < in them. If there is always a < Then just use the ELSE section instead.

SELECT
  CASE WHEN CHARINDEX('<', Message) = 0 THEN Message ELSE LEFT(Message, CHARINDEX('<', Message)-1)) END AS Message
FROM
  yourTable
GROUP BY
  CASE WHEN CHARINDEX('<', Message) = 0 THEN Message ELSE LEFT(Message, CHARINDEX('<', Message)-1)) END