Geek asks and answers

How to write a query that will make unusual choices from sql table?

Suppose I have a table in an 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 <1@b.com> |
|-------------------------------------------|
|    mailbox unavailable: 550 550 <2@b.com> |
|-------------------------------------------|
|                 ...                       |

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

  • account rejected:
  • Mailbox not available: 550 550

I thought to write a query that will select strings, exclude characters after the '<' sign and will do DISTINCT, but I do not know how to do it all inSELECT

Can you help me write such a request?

+5
source share
1 answer

The following deletes everything from the first to the <next.

GROUP BY , DISTINCT, .

CASE , <. < ELSE.

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

More articles:


All Articles