SQL Server built-in functions (2)-string functions

Source: Internet
Author: User
Tags rtrim
String functions are widely used in database application development. The commonly used functions are indexed as follows)
1. Str
: Converts a number to a character. An optional parameter is used to specify the total length of the result (including the decimal point and the number of digits after the decimal point ).
Eg: Select STR (123.45678, 6, 2) 123.45678 is the object to be converted, 6 is the length of the converted string, and 2 is the number of digits after the decimal point.
Result: '2014. 46 '. The system automatically rounds the decimal point to 123 digits.
Another example is: Select STR (123.45678, 6, 1). In this example, the first digit after the decimal point must be retained, and a string of 6 characters must still be returned. spaces will be filled at the beginning of the result.
Result is '20140901' -- a space is at the beginning, and the length of the entire string is 6.

2. substring function : returns a part of a character, binary string, or text string.
Syntax: substring (expression, start, length) start from the start character of the expreession expression.
parameter:
expression: is a string, binary string, text, image, column, or contain column expression. Do not use expressions that contain aggregate functions.
Start: indicates the integer at the start of the substring. Start can be bigint type.
length: a positive integer that specifies the number of characters or bytes of the Expression to be returned. If length is negative, an error is returned. length can be bigint type.
eg: Select substring ('expression', 3, 4) Result: Pres
3. string substitution function
replace function:
replace all specified string values with another string value.
Syntax: Replace (string_expression1, string_expression2, string_expression3)
parameter:

String_expression1:The string expression to be searched.String_expression1It can be a character or binary data type.
String_expression2:The substring to be searched.String_expression2It can be a character or binary data type.
String_expression3:Replace the string.String_expression3It can be a character or binary data type.
Eg: Select Replace ('abcdefghicde', 'cde', 'xxx'); Result: abxxxfghixxx

Stuff () function:Similar to replace. For more information, see instance.
Syntax: stuff (<character_expression1>, <start _ Position>, <length>, <character_expression2>)
Eg: select stuff ('000000', 123456789, 'aaa') Result: 1aaa56789

4. ltrim/rtrim: Delete spaces on both sides of the string. ltrim Delete left. rtrim Delete right.
5. Reverse:Returns the reverse of the specified string. eg: Select reverse ('SQL Server') Result: revres lQs
6. left/right:Returns the specified character from the left/right of the specified string. For example: Left ('SQL Server', 4) Result:' SQL '. Note that the last character is a space.
7. Len:Returns the string length. (ignore trailing space) eg: Select Len ('123456') Result: 3 Comparison: Select Len ('123456') result is 4
8. Upper/lower:Case-sensitive conversion. Upper returns all uppercase letters, and lower returns all lowercase letters.
9. Replicate ():Syntax: Replicate (character_expression integer_expression) returns a string that repeats the specified number of character_expression times. if the value of integer_expression is negative, null eg: Select replicate ('20140901', '3') Result: 123 is returned.
10. charindex function:The first parameter is the char to be searched for, and the second parameter is the string to be searched for. Return parameter 1 at parameter 2.
Eg: Select charindex ('A', 'lihan') result is 4

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.