String functions in SQL Server

/*************************************** * ********/-- String function /************************** * ********************/print char (ASCII ('A ')) -- return the location where L appears for the first time in hello -- (this is a strange phenomenon. Only in the AdventureWorks database, the preceding L characters must be case sensitive, it should be where the database is set.) print charindex ('l', 'Hello World') -- [AdventureWorks: 4, other databases: 3] -- it seems that this function is not much different from CHARINDEX. In fact, this more powerful print patindex ('% L %', 'Hello World') -- [AdventureWorks: 4, other databases: 3] -- Mode wl cannot be found in the expression (0). [wl] indicates Any one of w and l, in hello world, locate l -- [^ wl] In position (3): indicates any character not in wl, in (1) hSELECT PATINDEX ('% wl %', 'Hello World'), PATINDEX ('% [wl] %', 'Hello World '), PATINDEX ('% [^ wl] %', 'Hello World') -- [, 1] -- DIFFERENCE this function actually compares the SOUNDEX () of two strings () value similarity [3] select soundex ('hangel'), SOUNDEX ('hello'), DIFFERENCE ('El', 'Hello') print left ('Hello world', 4) -- Take the character [hell] print right ('Hello world', 6) on the left -- Take the character on the RIGHT. You can use Len () to check [world] print len (RIGHT ('He Llo world ', 5) -- The character length in the expression [5] select len (RTRIM (LTRIM ('Hello World'), LEN ('Hello World '), LEN ('Hello World') -- do not use the LEN function to calculate only spaces on the left and in the middle of the character. Do not use the space on the right [, 13, 13] print lower ('Hello World ') -- convert to lowercase [hello word] print upper ('Hello World') -- convert to uppercase [hello word] select len ('Hello World '), LEN (LTRIM ('Hello World') -- remove the left space [] select len ('Hello World'), LEN (RTRIM ('Hello World ')), RTRIM ('Hello World') -- remove the right space [11,11, HELLO world] select len (RTRIM (LTRIM ('Hello World'), RTRIM (LTRIM ('Hello World') -- Remove left and right spaces [11, HELLO world] print reverse ('Hello World') -- REVERSE expression [dlrow OLLEH] PRINT 'hello' + SPACE (4) + 'World' -- get SPACE, [hello world]/* char a = STR (float_expression [, length [, decimal]); length: total length. It includes the decimal point, symbol, number, and space. The default value is 10. Decimal: the number of digits after the decimal point. Decimal must be less than or equal to 16. If the decimal value is greater than 16, the result is truncated to keep it as 16 digits after the decimal point. Return: char type. Note: length should not be less than the number of digits in the integer part of the float_expression expression. Otherwise, length x is displayed. length> = LEN (FLOOR (float_expression). In order not to allow spaces on the left to be added: length = LEN (FLOOR (float_expression) + 1 (decimal point) + number of decimal places */print str (1223445.3, 2) -- because it is smaller than the number of integer parts (7) bit [**] print str (1223445.3, 5) -- because it is smaller than the number of integer parts (7) bit [*****] DECLARE @ num float set @ num = 323.14159 -- if the length is not enough, select len (STR (@ num,) is added with spaces on the left )), STR (@ num, 10,323.14) [] -- LEN (FLOOR (@ num) + 1 + 2: Here, select str (@ num, LEN (FLOOR (@ num) + 3, 2), CAST (STR (@ num, 10, 2) as float) -- from the position of the expression (Space), take a character (w ), use DELETE to replace print stuff ('Hello world', 6,2, 'delete') -- [helloDELETEorld]/* SUBSTRING (value_expression, start_expression, length_expression) 0 <= start_expression <= LEN (value_expression) 0 <= length_expression <= LEN (value_expression) extract length_expression characters from the value_expression position start_expression */print substring ('Hello world', 2, 6) -- [ello w]

The above functions are also your own learning notes. Of course, if you want to use them properly in projects, it depends on your own needs!

Technorati label: sqlserver, string Function