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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.