I encountered a problem today: I want to convert the text "xxx.gif" in a field in the data table into "xxx.jpg". I don't know its specific name, I only know that it ends with gif.
Problem solved: update pet set petPhoto=substring(petPhoto,1,datalength(petPhoto)-3)+'jpg'
where petPhoto like '%.gif'
Pay attention to the matching characters: "%" matches any character of any length, "_" matches any single character, [A] matches anything starting with A, [^A] matches anything except what starts with A. Knowing functions is the key to solving problems (the following is reproduced from the Internet):
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), round, n is the number of decimal places to retain
abs(n) absolute value
sign(n), when n>0, returns 1, n=0, returns 0, n<0, returns -1
PI(), 3.1415....
rand(), rand(n), returns a random number between 0-1
3. String function
ascii(), convert characters to ASCII code, ASCII('abc') = 97
char(), convert ASCII code to character
low(), upper() case conversion
str(a,b,c) converts numbers to strings. a, is the string to be converted. b is the length after conversion, and c is the number of decimal places. str(123.456,8,2) = 123.46
ltrim(), rtrim() removes spaces, ltrim removes spaces on the left, and rtrim removes spaces on the right.
left(n), right(n), substring(str, start,length) intercept string
charindex (substring, parent string), find whether it is contained. Returns the position of the first occurrence, without returning 0
patindex('%pattern%', expression) has the same function as above, but uses wildcards
replicate('char', rep_time), repeat string
reverse(char), reverse the string
replace(str, strold, strnew) replace string
space(n), generates n blank lines
stuff(), SELECT STUFF('abcdef', 2, 3, 'ijklmn') ='aijklmnef', 2 is the starting 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 function:
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 function
day(), month(), year()
dateadd(datepart, number, date), datapart specifies which part to add, number knows how much to add, and date specifies on whom to add. The values of datepart include year, quarter, month, dayofyear, day, week, hour, minute, second, such as tomorrow's dateadd(day,1, getdate())
datediff(datepart,date1,date2). datapart is the same as above. The entire function result is date2 - date1
datename(datepart, date) takes that part and returns a string.
datepart(datepart, date) takes a part and returns an integer.
getdate() current time
5. System function
col_length('tablename','colname')
col_name, SELECT COL_NAME(OBJECT_ID('Employees'), 1) = EmployeeID
datalength, example: datalenght('abc') =3, datalength(pub_name) --column name