Take a string from SQL Server

Question: assume that the structure of a table is (student ID, name, email address)

Check whether there are duplicate students; check whether there are duplicate email addresses; and finally count the number of students on different email servers, for example, cbmichael@163.com richman@163.com is 163.com mail in Statistics

A: whether there are duplicate names can be solved using group by. The most common

Select count (sname)

Fromfrom Table1

Group by sname

If each item is 1, it indicates no repetition.

There is also a method of comparative method.

Execute the following statements separately

Select count (distinct sname)

From Table1

And

Select count (sname)

From Table1

Check whether the two query results are consistent. If they are consistent, they indicate that there are no duplicates. Otherwise, duplicate names exist.

 

Similarly, the preceding two methods are used to determine whether there are duplicate email addresses. You only need to replace sanme with email.

 

Next, the most difficult part: count the number of students on different email servers.

Idea: first, it is easy to count the number of students. The difficulty lies in how to count different email servers.

For example, the cbmichael@163.com richman@163.com is a 163.com email in statistics.

That is to say, what we really want to calculate is the part following! Here I first thought about getting the string part. I remember that there was a mid function in Excel that could work like this. Now I want to change the SQL and find the function... It seems like a day .. Baidu constantly .. Finally found substring!

First, let's look at the statement:

Select substring (email, charindex ('@', email) + 1,100) email server, count (email) Student count

From Table1

Group by substring (email, charindex ('@', email) + 1,100)

Shard resolution...

Substring is a function for obtaining strings. Its basic usage is

Syntax
Substring (e-xpression, start, length)

Parameters
E-xpression
Is a string, binary string, text, image, column, or expression that contains a column. Do not use expressions that contain aggregate functions.
Start
It is an integer that refers to the starting position of the stator string.
Length
It is an integer that refers to the length of the substring (the number of characters to return or the number of bytes ).

So we started with subrsting (email,

 

Next

For example:

Charindex ('SQL', 'Microsoft SQL Server ')

 

This function command returns the starting position of "SQL" in "Microsoft SQL Server". In this example, the charindex function returns the position 11 of "S" in "Microsoft SQL Server ".

We use

Charindex ('@', email), get the position of @ in the email field, because it is the server address, therefore, "+ 1" indicates that it is the first character to the right from the position "@", and "100" indicates the length, because the address length of each mail server is different, such as 163.com, Hotmail, com, so we should take 100 for insurance. This completes the task of getting different email servers :)

 

Another method:

Select right (email, Len (email)-charindex ('@', email) email server, count (email) Users

From Table1

Group by right (email, Len (email)-charindex ('@', email ))

 

This method is easy to understand. First, declare that the right, Len, and charindex functions here are all string functions.

Usage: Right (char_expr, int_expr) returns int_expr characters to the right of the string

Right (email, Len (email)-charindex ('@', email) indicates that the Len (email)-charindex ('@', the length of the email. Len (email)-charindex ('@', email) indicates the total number of characters minus the number of characters before @ (including, the length of the remaining characters is the email server address :)

 

It is recommended that you use Baidu to check the SQL string retrieval method, which will certainly benefit a lot.