Second, string functions
General form:
SELECT FUNCTION_NAME (ARGUMENTS)
Splicing binary or character expression:
SELECT (Expression Expression .........)
When splicing non-character, non-binary expressions, you must use the CONVERT function to convert it to a character type or binary type data;
Such as: " Convert (VARCHAR (12), SALARY) from auths where sex = 0
?
Most of the string functions can only be used for CHAR and VARCHAR and those data types that can be implicitly converted into CHAR and VARCHAR.
Splicing can be used in Binary and Varbinary columns, or it can be used on the char and varchar columns.
Strings can nested and available in any place where the expression is present. When the constant is used together with the string function, the constant should be enclosed in single quotes or double quotes.
?
String function
Char_expr is Char, VARCHAR, BINARY, or VARBINARY type character data expression
Integer_expr is a positive integer
Pattern is a char_expr for pattern matching
FLOAT_EXPR is a floating point type value
?
Function name
parameter
Return the result
ASCII
(char_expr)
Returns ASCII encoding of the first character in the expression
Charr
(Integer_expr)
Convert a single-byte Integer value to a CHARACTER value. Char is usually used as a reverse function of ASCII. Integer_expr must be the number between 0 and 255.
Returns the CHAR data type. If the result value is the first byte of the multi-byte character, the character will be undecided.
Charindex
(Expression1, Expression2)
Find the initial position of Expression1 in Expression2 and return an Integer integer value indicating the starting position. If you don't find Expression1, it returns 0. If Expression1 contains a wildcard, it is handled by text.
Char_length
(char_expr)
Returns the length of a string expression or a Text value. For the variable length data columns in the table, remove the tail space when calculating the character expression length. For multi-byte character sets, the character length is less than byte, using system functions to determine the number of bytes.
Difference
(Char_EXPR1, CHAR_EXPR2)
Returns an integer value different from the two SoundEx values.
Lower
(char_expr)
Conversion of uppercase letters into lowercase letters, returns a Character type value
ITrim
(char_expr)
Delete the front guide space in a string expression. Only characters equivalent to the space value in the character set specified in SQL are deleted.
Patindex
("% Pattern%",
CHAR_EXPR [use {bytes |
Chars | characters}])
Returns an Integer type data of the starting position of Pattern in the specified string, and if Pattern is not found, it returns 0. By default, the offset returns the shift (a string of multiple bytes) must specify Using Bytes. The wildcard "%" must be placed in front or behind Pattern. Unless the location you are looking for is the starting character or end character. This function can be used for TEXT data
Replicate
(char_expr, integer_expr)
Returns a string of the same type as char_expr. The string is the result of Char_EXPR (when the result is more than 255 bytes, only the first 255 bytes).
Reverse
(Expression)
Reversible string or binary data. Returns "DCBA" if the expression is "abcd"; if the expression is "0x12345000", return "0x00054321
Right
(Expression, Integer_expr) Returns a part of the string or binary expression from the right side. Return Value with the same data type as expressions
RTRIM
(char_expr)
Delete tail spaces. Only characters equivalent to the space value in the character set specified in SQL are deleted.
Soundex
(char_expr)
Returns a 4-character SoundEx code consisting of a string consisting of adjacent single-byte or double-byte physical letters
Space
(Integer_expr)
Returns the string of integer_expr single-byte spaces
Str
(Approx_Numeric [, Length [, Decimal]])
Returns a string representing the floating point number. Length Specifies the number of strings (including all numbers, decimal points, all numbers and spaces); decimal specifies the decimal number returned; Length and Decimal are optional, if selected, must be non-negative numbers. The default Length is 10, the province's decimal is 0. In order to match the specified Length, the function is rounded into the fractional part.
Stuff
(char_expr1, start, length, char_expr2)
Remove the LENGTH character starting from Char_EXPR1, then insert char_expr2 into the Start of Char_EXPR2. If you only delete characters without inserting characters, you must specify CHAR_EXPR2 as NULL, not "", "indicates a space.
Substring
(Expression, Start, Length)
Returns part of the string or binary clock. Start specifies the start position of the substring. Length specifies the length of the substring.
Upper
(char_expr)
Convert lowercase strings to uppercase string binary string column names or combinations thereof. Place the string in single quotes or double quotes
EXPRESSION
Stitching two or more strings, binary strings, column names, or combinations thereof. Play the string in single quotes or double quotes.
?
Here is an example of using a string function.
Example 1: Substring function
The city where all writers who have been born after November 11, 58:
SELECT NAME, SUBSTRING (Address, 1, 4)
From auths
WHERE BIRTHDATE> "19588/11/11"
result:
Name
Wang Dalin ??? Beijing
Xu Lingqi ??? Harmony
Zhang Qi Di ??? Beijing
Hao Dan Ying ???? Shanghai
Wang Jun ????? Shanghai
Example 2: Use of Charindex and Patindex functions
Both functions returns a starting position for specifying character expressions or binary expressions. They all require two parameters, but they have a little difference.
Charindex is only available on the char and VARCHAR columns, while PatIndex can also be used on the Text column; PatIndex can use wildcards, while Charindex can't.
They all have two parameters:
The first parameter is a character express or binary expression that specifies its location.
For PatIndex, character expressions or binary expressions must be enclosed in "%" unless the expression is looking for the starting string (which can omit "%") or the last string (can omit the tail "%" ).
For CharIndex, a wildcard is not included in the expression.
The second parameter is a string expression, usually a column name. Adaptive Server will find the specified character express or binary expression.
Use the above two functions to find out the query statement of the start position of the title column string "Microsoft" in the table auths is:
Select Charindex ("Microsoft", Title, Patindex ("% Microsoft%", Article_Text)
From article
Where author_code = "A00001" Result:
-
1 ????????????????????? 1
0 ????????????????????? 1
Patindex Find out the table sysObjects starting with SYS, and the 4 characters are all rows of A, B, C, and D.
Select Name
From sysobjects
WHERE Patindex ("Sys [A-D]%", Name> 0
result:
--Sysalternates
Sysattributes
Syscolumns
Syscomments
Sysdepends
Example 3: Using STR functions
The STR function is used to convert a number of FLOAT types into strings. This function comes with an option of specifying an integer length (a number of decimal points) and the number of decimal points. For STR, the length and decimal parameters should not be negative.
The default of the length is 10. The specified length should be at least equal to the part of the number before the decimal point before the number of points (if any), ie:
??? length> = integer part positive and negative
If FLOAT_EXPR exceeds the specified length, the result is returned to the string "**" of the specified length.
?
SELECT STR (123.45, 2, 2)? Results: **
SELECT STR (123.45, 6, 2)? Results: 123.45
Example 4: Use the stuff function
The stuff function plugs a string into another string. It first deletes characters from the specified length starting from the starting position in EXPR1, and then inserts the expr2 string from the starting position. If the starting position or length is negative, an null string is returned. If the starting position is longer than EXPR1, an null string is returned. If the deletion length is longer than the expr1, the last character in EXPR1 is deleted.
Select Stuff ("ABC", 2, 3, "RGF") ???? Result: Argf
?
Some functions
1, stitching
Syntax: SELECT (Expression Expression ...)
SELECT ("abc" "def") ?????????? result: abcdef
?
SELECT "Writer code list" = "(" Name ")" "writer's code is:" Author_code
From auths
Where author_code <'A0007'
result:
Writer code list
-
(Wang Dalin) writer's code is: A0001
?
To splicing data or date types, you must use a conversion function Convert
Select Name "Birth Date is:" Convert (varchar (30), birthdate)
From auths where sex = 0
result:
-
Wang Dalin's birth date is: NOV 12 1958 12:00 AM
2, stitching and empty string
Empty string (a pair of double quotes or pair of single quotes), is treated as a space.
SELECT "ABC" "" "DEF"
result:
-
ABC DEF
3, the nesting of string functions
Show the name of the female writer and its birth year
SELECT NAME "Born in" Substring (Convert (varchar (30), birthdate), 8, 4) "year
From auths where sex = 0
result:
- Wang Da Lin lived in 1958
?
Show female writer's payment
Select Name "salary is:" Str (Salary, 5, 1) from auths where sex = 0
RESULTS: Wang Dalin's salary is: 120.0
