11-02c# base--string function of database

Database function

One, built-in functions -- String Functions

1 、--ASCII ASCII encoding of the first letter of the returned string

Select ASCII (' ame ')

Format in select ASCII (xingming) from xuesheng-- query statements

select*from haha where ASCII (name) >200--the ASCII code in the query table is greater than

2 、--CHAE converts ASCII code to the corresponding character

Select CHAR-- represents the Enter key in the ASCII code , showing spaces

Select CHAR (202)-- different from, represents no

Select CHAR from haha-- in the format of the query statement

3 、--CHARINDEX finds the string, returns the index of the first letter that matches the condition, and returns the index from the beginning, indicating no query to

Select CHARINDEX (' EFG ', ' abcdefghijklmnopqrstuvwxyz ')-- Returns the index of the first letter of the string

Select CHARINDEX (' 199 ', CID) from the haha--index from the beginning, returning to indicate no query to

4 、--CONCAT string splicing version can be used

5 、--Select CONCAT (' abc ' + ' BCD ') with Select (' abc ' + ' BCD ' + ' EFG ')

6 、--difference , return similarity (,,,,) %, similarity high % return, low similarity % return

Select difference (' abcefgr ', ' AB ')

7,--format string format version of the

8 、--left-to- right intercept string to specify length , return Int value, equivalent to substring intercept function

Select Left (' Abcdefgh ', 4)

Select Left (cid,14) the From haha-- column name equals the variable name, and the statement is equivalent to a for loop, one sentence execution,

9 、--LEN Returns the length of the truncated string , the front space is reserved, and the trailing space is automatically removed, equivalent to lengths

Select LEN (' 1234 ')

、--LOWER Convert uppercase letters to lowercase

Select LOWER (' ABCDEFG ')

One by one 、--LTRIM remove the left space, that is, the space in front of the string, equivalent to trimstart

Select LTRIM (' B abcdne ')

、--PATINDEX finds strings and numbers , starting at the beginning of the first occurrence of the query string (the string to be looked up before and after %)

Select PATINDEX ('%j% ', ' abcdefghijklmn ')

Select PATINDEX ('%67% ', ' 12345656787 ')

、--Replace replacement requires three parameters ( the target string, the target character segment to replace, and the replaced content )

Select REPLACE (CID, ' 606 ', ' 103 ') from haha

These functions only change to the results we want to see when displayed, but do not have any effect on the original data.

Select REPLICATE (' ABC ', 3)

、--REVERSE Flip String

Select REVERSE (' ABCDEFGHIJKLMN ')

Select REVERSE (name) from haha

、--right to the left to return a string by a specified number

Select Right (' Abschge ', 3)

16,--1rtrim Remove the right space

Select RTRIM (' ABNDC ASN SD ')

17,--space Print a space, in parentheses to write a few space to print a few

Select ' A ' +space (Ten) + ' B '

、--STR Converts a decimal number to a string, the first parameter is the original data of the string, the second argument is the length of the reserved string (including the decimal point), and the third parameter is the number of digits after the

Select STR (2131.23123,7,3)

、--STUFF Inserts the specified string in the string (removes the specified length character at the beginning of the specified index, and then inserts the specified character at the specified index)

--The first parameter is the original string, the second argument is the position at which the index begins, and the third parameter is the length of the specified deletion

--The fourth parameter is a string that specifies the input

Select STUFF (' ABC1234 ', 4,0, ' HELLOWORLD ')

A string that intercepts the specified length from the specified index 、--1SUBSTRING

Select SUBSTRING (cid,7,8) from haha

、--1UPPER string all uppercase

Select UPPER (' ABNSDKF ')

Key memory: Upper all uppercase, lower all lowercase, substring intercept string, Len intercepts the length of the string, RTrim from right to left, LTrim from left to right, charindex find string, returns the index of the qualifying string

Second, time and date function

1, select @ @DATEFIRST-2 @ at the beginning of the system constant, the return is the day of the week
2 、--1DATEADD Add a time period to the specified date, the first parameter is the type that represents the data to be added, the second parameter is the data to be added, the third parameter is the specified date time
Select DATEADD (year,2, ' 2006-7-31 ')
3,--1 to find the time difference, you can specify the type; The first parameter is the specified date type, and the second to third parameter is the specified datetime
Select DATEDIFF (year, ' 2011-7-31 ', ' 2014-11-2 ')
4,--datename (returns a string) returns a time value, the number of the desired block type, such as month and day can be independently obtained year or month or day
Select Datename (Weekday, ' 2014-11-2 ')
5 、--1DATEPART (return value is int type) calculated from Sunday is the first day
Select DATEPART (WEEKDAY, ' 2014-11-25 ')
Select DATEPART (WEEK, ' 2014-5-15 ')
6 、--Day Returns the date when it is the current month, and returns an int value
Select Day (' 2014-11-25 ')
7 、--1GETDATE Gets the date and time of the server on which the current database resides
Select GETDATE () as normal
8 、--ISDATE judge the input to make its time correct, return 1 correctly, error return 0;
Select ISDATE (' 2013-2-29 ')
9.--month of time value
Select MONTH (' 2014-2-15 ')
、--Get the system time
Select Sysdatetime ()
11.--Get the year
Select year (' 2014-7-11 ')
Three 、------type conversions, conversion functions------
--Three ways to convert pause convert cast
1. CAST

Select CAST (123 as varchar (20))
Select CAST (123 as int)
Select CAST (123 as Decimal (18,2))
Select CAST (' 12.3 ') as decimal (18,2)
2 、--Convert front write type, followed by write target data
Select CONVERT (int, ' 12.3 ')
3 、--Pause Version 2012
Select Pause ()
Practice: To intercept the student's identity card information in the month and day
Select*from Xuesheng
ALTER TABLE Xuesheng add ID varchar (20)
Update Xuesheng Set ID = ' 370305199202152341 '
Method 1:select Xingming,stuff (SUBSTRING (id,7,6), 5, 0, ' year ') + ' month ' +substring (id,13,2) + ' Day ' from Xuesheng
Method 2:select xingming, SUBSTRING (id,7,4) + ' year ' +substring (id,11,2) + ' month ' +substring (id,13,2) + ' Day ' as ' birthday ' from Xuesheng

ALTER TABLE Xuesheng add ID1 varchar (20)
Update Xuesheng Set ID1 = ' February 15, 1993 '

11-02c# base--string function of database