Today I encountered a problem: to convert the text in a field in the data table to "xxx.gif" to "xxx.jpg", I don't know the specific name, but I only know that it ends with GIF.
Solution: Update pet set petphoto = substring (petphoto, 1, datalength (petphoto)-3) + 'jpg'
Where petphoto like '{.gif'
Note: "%" is used to match any character of any length. "_" matches a single character. [a] matches a character starting with, [^ A] match the delimiters starting with. Knowing the function is the key to solving the problem (from the network below ):
1. Statistical functions AVG, Count, Max, Min, Sum
2. mathematical functions
Ceiling (n) returns the smallest integer greater than or equal to n.
Floor (N), returns the largest integer less than or equal to n
Round (m, n), rounding, n is the number of digits that keep decimal places
ABS (n) absolute value
Sign (N). If n> 0, 1, n = 0 is returned. 0, n <0,-1 is returned.
Pi (), 3. 1415 ....
Rand (), Rand (N), returns a random number between 0 and 1.
3. String Functions
ASCII (), which converts characters to ASCII codes. ASCII ('abc') = 97
Char (), ASCII code converted to character
Low (), upper () case-insensitive Conversion
STR (a, B, c) converts a number to a string. A is the string to be converted. B Indicates the length after conversion, and C indicates the number of decimal places. STR (123.456, 123.46) =
Ltrim (), rtrim () to the space ltrim to the left space, rtrim to the right space
Left (N), right (N), substring (STR, start, length) truncated string
Charindex (sub-string, parent string), to find whether it contains. Returns the location where the first message appears. No value is returned.
Patindex ('% pattern %', expression) is the same as above, but Wildcards are used.
Replicate ('Char ', rep_time), repeat the string
Reverse (char), reverse string
Replace (STR, strold, strnew) replace string
Space (n) to generate n empty rows
Stuff (), select stuff ('abcdef', 2, 3, 'ijklmn ') = 'aijklmnef', 2 is the start position, 3 is the length of characters to be deleted from the original string, and ijlmn is the string to be inserted.
3. type conversion functions:
Cast, cast (expression as data_type), example:
Select substring (title, 1, 30) as title, ytd_sales from titles where cast (ytd_sales as char (20) Like '3%'
Convert (data_type, expression)
4. Date Functions
Day (), month (), Year ()
Dateadd (datepart, number, date), datapart specifies to add that part, number knows how much to add, and date specifies the base on which to add. Datepart values include year, quarter, month, dayofyear, day, week, hour, minute, and second. For example, dateadd (day, 1, getdate () tomorrow ())
Datediff (datepart, date1, date2). datapart is the same as above. The result of the entire function is date2-date1.
Take the part of datepart (date) and return the string.
Datepart (datepart, date) is a part and an integer is returned.
Getdate () Current Time
5. system functions
Col_length ('tablename', 'colname ')
Col_name, select col_name (object_id ('ployees'), 1) = employeeid
Datalength, example: datalenght ('abc') = 3, datalength (pub_name) -- column name