2014-11-27 String functions and Queries

One, String function Functions

ASCII: Returns the ASCII code of the leftmost letter of the string

corresponding to char, convert ASCII code to character

charindex: Searches for another expression in one expression and returns its location

Example: Print charindex (' de ', ' ABCDEFG ')---with a result of 4, a comma before the found content, and then the location of the lookup, the result is the first few

Soundex: Converts a string to a 4-bit encoding (1-bit letter + 3-digit number)

Difference: Compares the SOUNDEX values of two strings with a few repetitions

Space: Play Space Example: Select Space (10)--10 spaces

Left: Returns the specified number of characters in the string

Right: Returns the specified number of characters in the string, but outputs the output from left to right

Example: Select Right (' Abcdefgh ', 3)---The result is FGH

Len: Returns the length of the string (without a space at the end of the sentence)

Lower: capitalize to smaller

Upper: smaller to uppercase

ltrim: Remove Space before string

RTrim: Remove space after string

Partindex: Usage is consistent with charindex. But Partindex must add a wildcard "percent"

Replace: replaces.

Example: Select replace (' abcdefahhhh ', ' ab ', ' @# ')---The result is @ #cdefahhhh, the first is the target string, the second is the character that needs to be replaced, and the third is replaced with XX

Replicate: Copy.

Example: Select replicate (' AB ', 3)---The result is ' Ababab ', and 3 is the number of copies

Reverse: Returns the inverse of a string

STR: The float type is intercepted and converted to a string type

Example: Print str (12.123456,5,2)-the result is 12.12, the first number is the number that needs to be manipulated, the second is to keep several (including punctuation), and the third is to retain several

Stuff:print stuff (' abc123defgh ', 4,3, ' abc ')--the result is ' abcabcdefgh ', first removed and replaced

substring: Intercepting strings

Example: Select substring (' abcdef ', 3,1)---The result is C

Common functions:

Substring,reverse,replace,ltrim,rtrim,lower,upper,len,left,right,charindex

Second, type conversion function

Cast:select cast (' 123 ' as int)---convert 123 to int

Convert:select convert (int, ' 123 ')

Third, connection query

1.join......on ...

Example: Select Sno,score.cno,cname,degree from score join course on Score.cno=course.cno

The following can continue to connect the conditional join student on Student.sno=score.sno

You can add a modifier to a term before join, such as a left Join,right join (formerly one or the last table as the baseline)

2. Two tables are stitched directly, followed by where to establish the condition

Example: Select Score.sco,sname,sco,degree from Score,course where Score.con=course.cno

3. Vertical connection: Connect two tables in the same column

Example: Select Sname,ssex from student uinon select Tname,tsex from teacher

2014-11-27 String functions and Queries