Database string, date, and data type conversions (Cast/convert)

Database (string function, Date function, data type conversion Cast,convert)

Use student

Go

CREATE TABLE Yuangong

(

code int,

Name varchar (20),

Sex varchar (20),

Age int,

Bumen varchar (20),

)

INSERT into Yuangong values (1, ' Week number ', ' Male ', 27, ' sales Department ')

INSERT into Yuangong values (1, ' King of the Week ', ' Male ', 27, ' sales Department ')

INSERT into Yuangong values (1, ' place ', ' Male ', 27, ' sales Department ')

INSERT into Yuangong values (1, ' Zhou Wen king ', ' Male ', 27, ' sales Department ')

INSERT into Yuangong values (1, ' See King ', ' Male ', 27, ' sales Department ')

INSERT into Yuangong values (1, ' Zhou Wang ', ' Male ', 36, ' sales Department ')

Go

Sp_renamedb xxx, FFF--Renaming the database! XXX changed into FFF

Alter table Xinxi Add [int] varchar (20)--Adds an int column to the Xinxi table, and the value type is varchar (20). The newly added column cannot be set to not be empty!

Alter table xinxi Drop column [int]--delete [int] Column

Update Xinxi set nianling=26 where Fenshu between and 100-modified. Change the score ~100 between the people and the age.

Select distinct name from xinxi– automatically removes duplicate name information!

################################################

ALTER TABLE Yuangong add CID varchar

Update Yuangong Set cid=370322198908120800

################################################

-- String Functions

--the first character of the returned string is ASCII encoded

Select ASCII (' ame ')--Returns an int value

Select ASCII (name) from Yuangong--note the application format

Select *from yuangong where ASCII (name) >200

--Convert characters to corresponding ASCII codes

Select CHAR (100)

Select CHAR (age) from Yuangong

--Check the string, return the qualifying Initial index, index from start, return representative not found

--(in C # IndexOf index from Start, return-1 means not found)

Select CHARINDEX (' EFG ', ' Abcdefghijklmnopqrstuvwsyz ')--this is displayed.

Select CHARINDEX (' 199 ', CID) from Yuangong--application to table usage

--string concatenation

Select ' 1 ' + ' abc ' + ' 2 ' + ' Def ' as by virtue of--execution, will be displayed (by: abc2def)

--Return similarity degree

Select difference (' abcdefgh ', ' abcdefgh ')--Returns the number ~4,4 represents exactly the same!

--truncate characters from left to right

Select Left (' ABCDEFG ', 3)--Indicates that a bit is intercepted from the right, showing ABC

Select Right (' ABCDEFG ', 3)--Indicates that the bit is intercepted

--Return string length

Select LEN (' A V ')--returns. The following spaces do not count, the front and the middle are counted!

--All small/uppercase

Select LOWER (' ASDFGCVX ')--Display ASDFGCVX

Select UPPER (' ABCDDDC ')--Display ABCDDDC

--from the left to the space

Select LTRIM (' 34332aaa ')

--when displayed, remove the left space! Display: 34332AAA

Select RTRIM (' 3234 ')

--when displayed, go to the right space! Showing: 3234

--Replacement

Select REPLACE (' 1234567890 ', ' 456 ', ' ABCD ')

--Show 123abcd7890. (The string to replace, the content to replace, the modified content)

Select REPLACE (CID, ' 606 ', ' 103 ') from Yuangong--formatting applied to the table

-Copy and paste

Select REPLICATE (' abc ', 3)--Copy ABC and paste secondary

--Sequential flipping

Select REVERSE (' ABCDE ')--Display EDCBA

Select REVERSE (name) from Yuangong--apply formatting to tables

--Space

Select ' A ' +space (a) + ' B '--splicing AB for easy observation! A space

-Intercept numbers

The Select STR (123456.789012345,10,9)--10 is the total number of digits that contain. 9 is the number that is retained after the decimal point. The previous number is the main.

--Replacement string

Select STUFF (' abcdefgh ', 4,2, ' hello ')--4 represents the beginning of the fourth index from left to right (starting from), and 2 represents the number of characters replaced. This displays the ABC Hello FGH

-Intercept string

Select SUBSTRING (' ABCDEFG ', 4,2)--intercepts the specified length string, starting at the specified index. This shows the de

--common: case, flip, charindex, substring, len

#################################################################

-- Date and time data types and functions

--show the day of the week

SELECT @ @DATEFIRST--Displays the day of the week and returns int

-Give a time to add some time

Select DATEADD (year,2, ' 2006-01-01 ')--show-01-01

--For the time difference (years, months, days, hours, minutes ...) is OK)

Select DATEDIFF (year, ' 2011-07-01 ', ' 2014-07-01 ')--display years.

Select DATEDIFF (Day, ' 2011-07-01 ', ' 2014-02-03 ')--show days.

--extracting years, months, days, hours, minutes, seconds, weeks, etc.

Select Datename (Weekday, ' 2014-11-02 ')--show ' Day ' is. ' Month ' is. ' Year ' Yes. The return value is Nvarchaer

Select DATEPART (WEEKDAY, ' 2014-11-02 ')--function ditto, return value int

Select year (' 2014-11-02 ')--extract years:, month, day. Note the format. function equals datepart

Select GETDATE () as current time--get database current time!

Select ISDATE (' 2013-06-31 ')--determine if the date format is correct, yes: 1, wrong: 0.

--Common IsDate, GETDATE, DATEPART,

Type conversions :cast,convert

Select CAST (123 as varchar (20))--The varchar type 123 is obtained.

Select CAST (1.23 as int)--the INT type 1 is obtained.

Select CAST (123 as Decimal (10,2))--The decimal type 123.00. This shows the total number of digits, 2 indicates the number of decimal places

Select CAST (12.3 as Decimal (18,2))--draws. 30.

Select CONVERT (int,123.45)

-Use the opposite of cast!

Select Name, (SUBSTRING (cid,7,4) + ' year ' +substring (cid,11,2)

+ ' Year ' +substring (cid,13,2) + ' month ') as birthday from Yuangong

########################################

CREATE TABLE Shengrii

(

Name varchar (29),

Birthday varchar (29),

)

Go

INSERT into shengrii values (' Zhang San ', ' 2014 Month Day ')

Select *from shengrii

Database string, date, and data type conversions (Cast/convert)