SQL Server connection Field Method

This article uses examples to parse SQL server connection fields ......

Today, icech of Western E wants to export some data of SQL Server. Originally, the year, month, and day are divided into three fields stored in staryear, starmonth, and starday, but the other's data is in one field, as follows:

Original data format

Staryear starmonth starday
2005 08 21
2005 08 22
2005 11 09
2005 12 11

The exported format is

Stardate

2005-08-21

Septem

2005-12-11

If the staryear, starmonth, and starday fields are both char-type

Western e-network

Use the SQL statement directly:

Select rtrim (staryear) + '-' + rtrim (starmonth) + '-' + rtrim (starday) As Stardate From Star

You can.

Note: rtrim removes spaces from the right side for format purposes. For other functions, see the bottom of this article.

If the staryear, starmonth, and starday fields are of the int type

The format of the field must be converted.

Select cast (staryear as varchar (4) + '-' + Cast (starmonth as varchar (2 )) + '-' + Cast (starday as varchar (2) as Stardate From Star

Note: cast is a conversion function. If the staryear field is a string or other format, conversion can also be performed. For more data type conversion functions, see the bottom of this article.

Western e-network

　　---- String function ----

ASCII () -- returns the ASCII value of the leftmost character of a character expression.

Char () -- function used to convert ASCII code to character

-- If no value is entered ~ The Char function returns a null value for the ASCII value between 255.

Lower () -- function converts all strings to lowercase letters

Upper () -- function converts all strings to uppercase

STR () -- function converts numeric data to numeric data

Ltrim () -- function removes spaces in the string Header

Rtrim () -- function removes spaces at the end of the string

Left (), right (), substring () -- The function returns some strings.

Charindex (), patindex () -- The function returns the starting position of a specified substring in the string.

Soundex () -- The function returns a four-digit escape code.

-- The soundex function can be used to find strings with similar sound. However, the soundex function returns only 0 values for numbers and Chinese characters.

Difference () -- the difference between the two character expressions returned by the soundex Function

-- 0 the first character returned by the two soundex functions is different

-- 1 the first character returned by the two soundex functions is the same

-- 2 two soundex functions return the same first and second characters.

-- 3 two soundex functions return the same first, second, and third characters.

-- 4 two soundex functions return identical values

Western e-network

　　---- Data type conversion function ----

The syntax of the cast () function is as follows:

Cast () (as [length])

The convert () function syntax is as follows:

Convert () ([length], [, style])