SQL Server data table fuzzy query (like usage) and query functions detailed explanation

1. Fuzzy query (like)

In SQL Server Management Studio (SSMS), fuzzy queries are mainly done by usinglikeOperators are implemented.likeOperators are used inwhereSearch for data in a column with the specified schema in the statement.
Let's learn about it in a simple example:

• %Wildcard: represents any number of characters.

  select * from table_name where column_name like '%pattern%';
  

  This example will be selectedcolumn_nameAll records containing the word "pattern" in the column, no matter what the content is, as long as the pattern is included, it will be found.

• _Wildcard: represents a single character.

  select * from table_name where column_name like '_pattern%';
  

  This example will be selectedcolumn_nameThe column starts with any single character, followed by all records of "pattern". For example: apattern, bpattern

• []Character set: Match any single character in brackets.

  select * from table_name where column_name like '[a-c]pattern%';
  

  This example will be selectedcolumn_nameThe column starts with "a", "b", or "c", followed by all records of "pattern".

• [^]or[!]Negative character set: does not match any single character in parentheses.

  select * from table_name where column_name like '[^a-c]pattern%';
  

  This example will be excludedcolumn_nameRecords in the column that start with "a", "b", or "c", but all records that start with other characters followed by "pattern" are selected.

• inCharacter set: does not match any single character in brackets. The in operator allows multiple values ​​to be specified in the where clause to test whether the value of a column is contained in the specified value list. If the value of the column matches any of the values ​​in the list, the record will be selected.

select StudentName,StudentAddress,Birthday from Students
where StudentName in('Wang Xiaoer','Xiao Dan')

This sentence selects the name, address and date of birth of the student whose name is "Wang Xiaoer" or "Xiao Dan".

uselikeWhen operating the%and_It can be placed anywhere in the pattern, and multiple wildcards can be used to construct complex search patterns. Additionally, fuzzy queries may affect database query performance, especially when wildcards appear at the beginning of the schema, as this prevents SQL Server from searching with indexes.

2. Query function

Let's summarize some more commonly used functions
In SQL Server Management Studio, there are a variety of built-in query functions that can be used to perform data retrieval and conversion. These functions belong to different categories, such as string functions, numerical functions, date and time functions, conversion functions, etc. The following are some commonly used SQL Server query functions classification and examples:

• String function:

  • len(string): Returns the length of the string.
  • charindex(substring, string): Returns the position of the substring in the string.
  • substring(string, start, length): Returns a substring of the string.
  • replace(string, old_substring, new_substring): Replaces all specified substrings in the string.
  • left(string, number_of_chars): Returns the specified number of characters to the left of the string.
  • right(string, number_of_chars): Returns the specified number of characters to the right of the string.
  • lower(string): Convert a string to lower case.
  • upper(string): Convert a string to uppercase.
  • trimi(string): Remove the space on the left side of the string.
  • rtrim(string): Remove the space to the right of the string.
  • concat(string1, string2, ...): Concatenate two or more strings.

• Numerical functions:

  • abs(number): Returns the absolute value of the number.
  • floor(number): Returns the maximum integer less than or equal to the specified number.
  • ceiling(number): Returns the smallest integer greater than or equal to the specified number.
  • round(number, decimals): Returns the value after the number is rounded to the specified decimal places.
  • SUM(column): Returns the sum of the values ​​of a column.
  • AVG(column): Returns the average value of a column.
  • MIN(column): Returns the minimum value of a column.
  • MAX(column): Returns the maximum value of a column.
    For example: For example, we have a table that counts student scores ScoreList

select sum(Csharp) as C# Total score from ScoreListselect Total number of people=count(*)from Students

select MAX(Csharp) as C#The highest, MIN(Csharp) as C# lowest score, AVG(Csharp) as C# average score from ScoreList

These SQL query statements are used to extract specific statistics from database tables:

select sum(Csharp) as C# total score from ScoreList

This query calculationScoreListIn the tableCsharpThe sum of the values ​​of the column (representing the student's C# score) and name the result asC# Total Results. The result of the query will be the sum of C# scores in all records.

select Total number of people = count(*) from Students

This query calculationStudentsThe number of records in the table, that is, the total number of students, and name this number asTotal number of people。count(*)The function calculates the total number of records in the table, including all rows, regardless of whether the column value is NULL.

select MAX(Csharp) as C# highest, MIN(Csharp) as C# lowest score, AVG(Csharp) as C# average score from ScoreList

This query is fromScoreListThree statistical data are extracted from the table:

• MAX(Csharp) as C# highest: The highest value of C# score, the result column is namedC# highest。
• MIN(Csharp) as C# lowest score: The lowest value of C# score, the result column is namedC# lowest score。
• AVG(Csharp) as C# average score: The average value of C# scores, the result column is namedC# average score。

• Date and time functions:

  • getdate(): Returns the date and time of the current system.
  • datepart(part, date): Returns the specified part of the date (such as year, month, and day).
  • datediff(part, startdate, enddate): Returns the difference between two dates.
  • dateadd(part, number, date): Add the specified time interval on the date.

• Conversion function:

  • cast(expression AS data_type): Converts an expression to the specified data type.
  • convert(data_type(length), expression, style): Convert data types and use style parameters.

• Aggregation function:

  • count(*): Calculate the number of records in the table.
  • cout(column): Calculate the number of non-NULL values ​​in a column.
  • group by: Used to group the result set by one or more columns in combination with aggregate functions.

• Other useful functions:

  • isnull(check_expression, replacement_value): Check whether an expression is null, and if so, returns an alternative value.
  • coalesce(expression1, expression2, ...): Returns the first non-NULL expression in the parameter list.

These functions can be used alone in queries, or combined with other SQL statements to improve data processing capabilities and flexibility. Through these functions, data can be processed and converted effectively to meet various complex business logic needs.

Summarize

This is the article about SQL Server data table fuzzy query (like usage) and query functions. For more related SQL Server data table fuzzy query content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!