SQL Server Learning Note <>sql in the range of lookups, data types, string handling functions

in-range lookup for SQL

(1) Between.....and usage

Usually we look for a record in a fixed area, and can write SQL statements using >=,<=, for example: Find all records with an order price between 1000 and 2000, which can be written like this:

1 SELECT * from Sales.ordervalues2 where val>=1000 and val<=2000

Query Result:

The sales.ordervalues here comes from a defined view, as discussed in the following view.

If you use Between.....and ..... can also achieve the same effect.

1 SELECT * from Sales.ordervalues2 where Val between and 2000

Note One thing: Between....and ..... is the boundary value that contains the boundary, that is, 1000 and 2000.

(2) in usage

If you want to find Customer No. 1th, customer 2nd, 9th customer order information, in general, we will write:

1 SELECT * from Sales.ordervalues2 where custid=1 or custid=2 or custid=9

The result is:

Use in, then reduce the complexity of the writing, you can do so as follows to meet the requirements.

1 SELECT * from Sales.ordervalues2 where CustID in (1,2,9)

(3) Like usage, used to match characters or strings.

If you want to find the employee table Hr.employees inside LastName contains a character. It can be written like this:

1  SELECT * from Hr.employees2  where LastName like '%a% '

The results shown are:

Where% represents a wildcard character, you can think of any characters.

SQL data Type

SQL contains a variety of data types to meet a variety of development needs. Common types of data include:

(1) Binary data type.

Binary data includes binary, Varbinary, and Image.
Binary data types can be either fixed-length (binary) or variable-length.
binary[(N)] is a fixed n-bit binary data. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.
varbinary[(N)] is a binary data of n-bit variable length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes, not n bytes.
The data stored in the Image data type is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, applications can store data in the Image data type using BMP, Tief, GIF, and JPEG formats.

(2) Character data type.

The types of character data include Char,varchar and Text.
Character data is data that is any combination of letters, symbols, and numbers.
Varchar is a variable-length character data whose length does not exceed 8KB. Char is a fixed-length character data with a length of up to 8KB. ASCII data over 8KB can be stored using the text data type. For example, because Html documents are all ASCII characters and are typically longer than 8KB in length, these documents can be stored in SQL Server with the Text data type.

(3) Unicode data type .

Unicode data types include Nchar,nvarchar and ntext.
In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server Setup, a character set is allowed to be selected. Enables A Unicode data type in which any character defined by the Unicode standard can be stored in a column. In the Unicode standard, all characters defined in various character sets are included. With the Unicode data type, the prevailing scenting is twice times the size of the scenting used by non-Unicode data types.

In SQL Server, Unicode data is stored in Nchar, Nvarchar, and Ntext data types. Columns stored with this type of character can store characters in multiple character sets. When the length of a column changes, you should use the nvarchar character type, where you can store up to 4,000 characters. When the length of a column is fixed, you should use the Nchar character type, and you can store up to 4,000 characters at this time. When you use the Ntext data type, the column can store more than 4,000 characters.

(4) Date and time data type.

Date and time data types include two types of Datetime and smalldatetime .

Date and time data types are made up of valid dates and times. For example, valid date and time data includes "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01am 8/17/98". The previous data type is the date before, the time in the last data type is a moment before, and the date in the back. In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when the date range is stored starting January 1, 1753 and ending December 31, 9999 (each value requires 8 Storage bytes). When using the smalldatetime data type, the stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage).
The format of the date can be set. The commands for formatting dates are as follows:
Set DateFormat {format | @format _var|
Where, Format | @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, when the set DateFormat YMD is executed, the date is formatted as a month and day form; When the set DateFormat DMY is executed, the date is in the form of sun and moon.

(5) numeric data type.

Numeric data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating-point numbers), and integers.
Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, the data types stored by integers are int,smallint and Tinyint. The INT data type stores data in a range that is larger than the Smallint data type stores the data, while the Smallint data type stores the range greater than the tinyint data type stores the data. The range of data that is stored using the INT data is from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When using the Smallint data type, the range of stored data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage). When you use the tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 bytes of storage space).
The exact data type for the data in SQL Server is Decimal and Numeric. The amount of storage space that this data occupies is determined by the number of bits in the data.
In SQL Server, the data type of the approximate decimal data is Float and Real. For example, One-third of this score is recorded. 3333333, which can be expressed accurately when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

Here's a little bit of illustration, for example:

We declare a variable, assign the value and print it out:

1  declare @t char (2),  set @t= ' Hello '; 3  print @t;

What is the output of @t when we continue the string connection operation for the @t variable?

1  declare @t char; 2  set @t= ' Hello '; 3  print @t;4  5  set @[email protected]+ ' World '; 6  Print @t

Results

At this point, fixed length char type, length is fixed, insufficient time to fill with space, when continue to add world to Hello, because has reached the maximum length, so cannot add

To. Now let's look at the use of varchar (10).

1  declare @t varchar; 2  set @t= ' Hello '; 3  print @t;4  5  set @[email protected]+ ' World '; 6  Print @t

Results:

So compared to char and varchar, the former is a fixed-length type, although the latter declares the character length, but is variable.

After adding a declaration variable, we do not copy it, then this variable is null. Null is an operation with any character or variable, and the result is null.

To ensure that there is a result, here we learn a string expression IsNull usage. IsNull ("Variable 1", desired assignment). If the variable is null, then the variable is assigned the desired assignment value.

The above variables are not assigned, so there is no result in string connection, here we can do this:

1  declare @t varchar (2  print @t;3  4  set @t=isnull (@t, ') + ' world '; 5  print @t6  

The result is:

String Handling Functions

(1) Substring substring.

For example: The name of the product is too long, we may need to intercept the main information.

The following part of the product is intercepted, that is, substring ("string", starting position, intercept length). It is also important to note that the SQL subscript starts at 1 and is not the same as in C #. Not counting starting from 0.

1  Select Productname,substring (productname,9,10) 2 from   production.products

Results:

(2) left indicates the Intercept from the right (from left to right), and right to intercept (from the left).

1  Select Productname,left (productname,9) 2 from   production.products

1  Select Productname,right (productname,9) 2 from   production.products

(3) Len returns the number of characters in the string, ignoring spaces.

1  declare @t char; 2  set @t= ' Hello '; 3  print @t;4  print len (@t); 5  set @t=isnull (@t, ') + ' World '; 6  print @t7  print len (@t)

Results:

As you can see from the results, Len returns the number of characters in the string.

(4) Datalength, returns the actual length of the string, including spaces.

1  declare @t char; 2  set @t= ' Hello '; 3  print @t;4  print datalength (@t); 5  set @t=isnull (@t, ') + ' World '; 6  print @t7  print datalength (@t)

(5) Charindex find out where a matching character appears in the string.

1  2  Select Productname,charindex (", ProductName) as N ' appears space position ' 3 from  production.products

(6) Replace replaces part of a string.

1  Select Productname,replace (ProductName, ' product ', ' name ') 2 from  production.products

Results:

(7) Stuff the substitution string, starting with the nth character, the number of M characters, and the corresponding string substitution.

For example:

1  Select Productname,stuff (productname,8,1, ':::: ') 2 from  production.products

(8) Case conversion. Upper uppercase, lower lowercase.

1   Select Productname,upper (ProductName), Lower (ProductName) 2 from  production.products

(9) Remove the space RTrim, remove the space from the right, LTrim remove the space from the left.

1  declare @s char (2),  set @s= ' Hello ', 3  Select  datalength (RTrim (@s));

Because there is no trim for SQL, all whitespace in the string is removed using:

Select LTrim (RTrim (@s));

SQL Server Learning Note <>sql in the range of lookups, data types, string handling functions