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.