SQL Server functions full solution < three > data type conversion functions and text image functions

Original: SQL Server function Full solution < three > data type conversion function and text image function

One: Data type conversion function

When you work with values of different data types at the same time, SQL Server typically automatically makes a hermit type conversion. Values that are similar to data types are valid, such as int and float, but for other data types, such as Integer and character types, the Hermit transform cannot be implemented, and the display transform must be used. To achieve this display conversion, T-SQL provides two display conversion functions, namely the cast and convert functions.
The cast (x as type) and CONVERT (type,x) functions convert a value of one type to a value of another type.
Eg: select cast (' 121231 ' as DATE), CAST (as CHAR (3)), CONVERT (Time, ' 2012-05-01 12:11:10 ')
As you can see, cast (' 121231 ' as date) converts the string value to the corresponding date value; cast (as CHAR (3)) converts the integer 100 to a String type with 3 display widths, resulting in the string "100"; Convert (Time, ' 2012-05-01 12:11:10 ') converts the value of the DateTime type to the time type value, and the result is "12:11:10.0000000".

Two: text and image Functions

Text and image functions are used to manipulate text or image input values or fields, and to provide basic information about the value. The commonly used text in T-SQL contains two functions, namely the TEXTPTR function and the Textvalid function.
1.TEXTPTR function 

The TEXTPTR (column) function is used to return the text pointer value of the varbinary format, in either text, ntext, or image fields. The found text pointer value can be applied to the Readtext,writetext and UPDATETEXT statements. Where the parameter column is a field column with a data type of text, ntext, or image.
"Example" query authors table in the Name field 16 byte text pointer;
First create the table authors, the Name field is the text type, and the T-SQL code is as follows:
 CREATE TABLE authors (ID int, name text);
INSERT into authors values (1, ' This is a text ');
Use TEXTPTR to query the 16-byte text pointer of the name field in the authors table
 Select ID, TEXTPTR (name) from authors where id=1

2. Textvalid function 

The Textvalid (' table.column ', text_ptr) function is used to check whether a specific text pointer is a valid text, ntext, or image function. Table.column for the specified data table and field, Text_ptr is the text pointer to be checked.
The example checks for a valid text pointer for each value in the Decription field in the authors table.
　　Select ID, ' This is a text ' = Textvalid ((' Authors.name '), TEXTPTR (name)) from authors;
The first 1 is the value of the ID, and the second 1 indicates that the value of the query exists.

　　

SQL Server functions full solution < three > data type conversion functions and text image functions