Notes for SQL Server String operations

There is a news data table tb_news, which has the news_id int and news_type int fields. The former is the primary key and the latter is the ID of the news topic. The requirements are as follows:

Given a string a_right, the string contains multiple news topic ID values, namely news_type, and the storage format is 'B' + news_type + 'V ', IDs are separated by commas,

Example: b1v, b2v, b3v ......

Now we want to query all the news in the News table. After the format of news_type is converted to 'B' + news_type + 'V', the news topic in this format is included in the news column in a_right.

The query statement to start writing is as follows:

Select * From tb_news where charindex ('B' + STR (news_type) + 'V', @ a_right)> = 1

The result execution result is very different from the expected result!

After repeated troubleshooting, I finally found out the cause: the STR () function of SQL Server has two parameters: STR (Express, length). If length is not specified, the default value is

After the string is converted into a string, 10 null characters are retained on the left of the string. For example, print 'B' + STR (2), the result is B 2. Therefore, the converted string must be removed from the null value,

In this case, you can use the ltrim () function. For example, run print 'B' + ltrim (STR (2) and the result is B2.

Change the preceding query statement:

Select * From tb_news where charindex ('B' + ltrim (STR (news_type) + 'V', @ a_right)> = 1

Smooth execution!

 

Appendix:

Description of a STR Function

Run the following command in SQL Server manage studio:

Print 'B' + STR (333, 1) Result: B3;

Print 'B' + STR (333, 2) Result: B33;

Print 'B' + STR (333, 4) Result: B *;

Print 'B' + STR (333): B 333

SQL Server has the following common functions for string operations:

1.1 length and Analysis

Datalength (char_expr) returns the number of characters in the string, but does not contain spaces

Substring (expression, start, length ).

Right (char_expr, int_expr) returns int_expr characters to the right of the string

1.2-character operation class

Convert upper (char_expr) to uppercase

Lower (char_expr) to lowercase

Space (int_expr) generates int_expr Spaces

Replicate (char_expr, int_expr) copies the string int_expr times

Reverse (char_expr) reverse string

Stuff (char_expr1, start, length, char_expr2) Replace the length characters starting from start in character char_expr1 with char_expr2

Ltrim (char_expr) removes spaces on the left; rtrim (char_expr) removes spaces on the right

The ASCII (char) Char (ASCII) function corresponds to each other, and the ASCII code is used.

1.3 string SEARCH

Charindex (char_expr, expression) returns the starting position of char_expr. The index starts from number 1, not 0.

Patindex ("% pattern %", expression) returns the starting position of the specified mode. Otherwise, it is 0.