String functions, data type conversion functions, connection queries

1. String Functions

--Converts the average height to ASC Code

Select ASCII (AVG (Shengao)) from SG

--Converts the maximum weight to a character type

Select CHAR (MAX (Tizhong)) from SG

--Returns a string of four-bit soundex code

Select SOUNDEX (' LSKD ')

--Compare two soundex codes with a few of the same

Select difference (' dgghsf ', ' DFS ')

-- Print spaces

Select SPACE (11)

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

--Find the index of the first initial letter of the qualifying character segment for the corresponding wildcard

Select PATINDEX ('%ds% ', ' SDSFDSFF ')

-- Copy

Select REPLICATE (' SSDD ', 2)

-- convert float type to character type after interception

Select STR (2322.32,7,1)

-- After removing a specified length of character segment from a specified position , Insert a new character segment

Select STUFF (' Jdsklfjsjl ', 4,3, ' AAA ')

- -Query the position of as in hgdkgjadfjhgashasghjghgf

Select CHARINDEX (' as ', ' HGDKGJADFJHGASHASGHJGHGF ')

-- intercept the opening position

Select Left (' Afsjflsfkldsks ', 3)

-- intercept End bit

Select Right (' SDFKJDSKKFLSJ ', 3)

-- after removing the trailing space , calculate the length , return the integer

Select LEN (' JDKFK jdsklfj ')

-- uppercase to lowercase

Select LOWER (' Ddsdj jddk ')

-- lowercase to uppercase

Select UPPER (' sdj dDK kfd ')

-- Remove the spaces in front of the string

Select LTRIM (' JDF dk ')

-- Remove the space after the string

Select RTRIM (' DS dd ')

-- Find and replace

Select REPLACE (' SDJKFJKD ', ' JK ', ' 111 ')

-- reversal

Select REVERSE (' Asdfsfgg ')

-- Intercept string

Select SUBSTRING (' Djfklsjdkl ', 6,2)

2.

-- data type conversion function

Select CAST (' 123 ' as int)

Select CONVERT (int, ' 123 ')

Cases:

Select *from sg

-- query name, gender, date of birth (with month date)

--substring

Select Xingming,xingbie,substring (CAST (Chushengriqi as varchar), 7,4) + ' year '+

SUBSTRING (CAST (Chushengriqi as varchar), (+) + ' month '+

SUBSTRING (CAST (Chushengriqi as varchar), 4,2) + ' day ' from SG

-- date plus conversion type splicing

Select Xingming,xingbie,cast (Year (Chushengriqi) as varchar) + ' years '+

CAST (Month (Chushengriqi) as varchar) + ' month '+

CAST (Day (Chushengriqi) as varchar) + ' days 'from SG

3. Connection Query

(1) Join on

Select Score.sno,sname,score.cno,cnome,degree from Score

Join Course on SCORE.CNO=COURSE.CNO

Join student on Student.sno=score.sno

(2) Two tables directly splicing,where to establish conditions

Select Score.sno,sname,cno,degree from Score,student

where Score.sno =student.sno

Select Teacher.tno, tname,cnome from teacher

Left JOIN course on Course.tno =teacher.tno

Select Teacher.tno, tname,cnome from teacher

Right join course on Course.tno =teacher.tno

Select Teacher.tno, tname,cnome from teacher

Join Course on Course.tno =teacher.tno

-- longitudinal connection

Select Sname,ssex from Student

Union

Select Tname,tsex from teacher

String functions, data type conversion functions, connection queries