The string truncation function can only be used for single-byte characters (garbled characters may occur when an odd number of Chinese characters are intercepted and must be processed separately). This function can intercept characters within a specified range of strings.
The string truncation function can only be used for single-byte characters (garbled characters may occur when an odd number of Chinese characters are intercepted and must be processed separately). This function can intercept characters within a specified range of strings.
SUBSTRING
Returns a part of a character, binary, text, or image expression. For valid Microsoft®SQL Server™For more information about data types, see data types.
Syntax
SUBSTRING (expression, start, length)
Parameters
Expression
Is a string, binary string, text, image, column, or expression that contains a column. Do not use expressions that contain aggregate functions.
Start
It is an integer that refers to the starting position of the stator string.
Length
It is an integer that refers to the length of the substring (the number of characters to return or the number of bytes ).
Substring ()
-- Obtain the substring from any position
Left ()
Right ()
-- Obtain the substring between the left and right sides
Ltrim ()
Rtrim ()
-- Truncates spaces without trim ().
Charindex ()
Patindex ()
-- Check the position of the substring in the parent string. no 0 is returned. Difference: patindex supports wildcards, but charindex does not.
Function functions:
The string truncation function can only be used for single-byte characters (garbled characters may occur when an odd number of Chinese characters are intercepted and must be processed separately). This function can intercept characters within a specified range of strings.
Application scope:
Title and content truncation
Function format:
String substr (string, int start [, int length])
Parameter 1: processing string
Parameter 2: start position of the truncation (the first character is from 0)
Parameter 3: Number of characters intercepted
For more information about substr (), see the official PHP Manual (string processing function library)
Example:
Substr ("ABCDEFG", 0); // return: ABCDEFG, intercepting all characters
Substr ("ABCDEFG", 2); // return: CDEFG, intercepting all characters starting from C
Substr ("ABCDEFG", 0, 3); // return: ABC, 3 characters starting from
Substr ("ABCDEFG", 0,100); // return: ABCDEFG, 100 although the maximum length of the pre-processed string is exceeded, the returned results are not affected. the system returns the maximum number of pre-processed strings.
Substr ("ABCDEFG", 0,-3); // return: EFG. Note that if-3 is a negative value, it indicates that the string is counted from the end and the position of the string remains unchanged.
Example:
1. intercept functions with known lengths
A. truncates N characters from the left of the string.
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'http: // www.163.com'
Select Left (@ S1, 4)
------------------------------------
Result: http
B. extract N characters starting from the right of the string (for example, www.163.com)
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'http: // www.163.com'
Select right (@ S1, 11)
------------------------------------
Result: www.163.com
C. truncate any position and length of a string (for example, www)
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'http: // www.163.com'
Select SUBSTRING (@ S1, 8, 3)
------------------------------------
Result: www.163.com
The above examples show the known truncation position and length. the following example shows the unknown position.
2. intercept functions with unknown locations
A. intercept the string after the specified string (for example, intercept the string after http)
Method 1:
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'http: // www.163.com'
Select Substring (@ S1, CHARINDEX ('WWW ', @ S1) + 1, Len (@ S1 ))
/* Here you can also write: Select Substring (@ S1, CHARINDEX ('//', @ S1) + 2, Len (@ S1 ))*/
------------------------------------
Result: www.163.com
Note: The CHARINDEX function is case insensitive when searching strings. Therefore, CHARINDEX ('WWW ', @ S1) can also be written as CHARINDEX ('WWW', @ S1)
Method 2: (similar to Method 1)
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'http: // www.163.com'
Select Substring (@ S1, PATINDEX ('% www %', @ S1) + 1, Len (@ S1 ))
-- This can also be written as follows: Select Substring (@ S1, PATINDEX ('% // %', @ S1) + 2, Len (@ S1 ))
------------------------------------
Result: www.163.com
The difference between the PATINDEX function and CHARINDEX function is that the former parameter can be used to increase the query function.
Method 3:
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'http: // www.163.com'
Select REPLACE (@ S1, 'http ://','')
------------------------------------
Result: www.163.com
Use the REPLACE function to REPLACE null characters except the strings to be displayed.
Method 4:
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'http: // www.163.com'
Select STUFF (@ S1, CHARINDEX ('http: // ', @ S1), Len ('http ://'),'')
------------------------------------
Result: www.163.com
The difference between the function STUFF and REPLACE is that the former can specify the replacement range, while the latter can be replaced within the whole range.
B. intercept the specified character string (for example, intercept the file name in C: \ Windows \ test.txt)
Different from A, when the search object is not one, the method above can only search for the first position.
Method 1:
The code is as follows:
Declare @ S1 varchar (100)
Select @ S1 = 'C: \ Windows \ test.txt'
Select right (@ S1, charindex ('\', REVERSE (@ S1)-1)
-------------------------------------
Result: text.txt
Use the REVERSE function to obtain the length of the string to be truncated.
Substr ()
Example:
The code is as follows:
Private void DDL_AreaBind ()
{
Conn = new SqlConnection (ConfigurationManager. ConnectionStrings ["strcon"]. ConnectionString );
String str = "0000 ";
Cmd = new SqlCommand ("select AreaID, Name = ltrim (Name) from Area where right (AreaID, 4) = '" + str + "'", conn );
SqlDataAdapter sda = new SqlDataAdapter (cmd );
Sda. Fill (ds, "area ");
This. ddl_area.DataSource = ds. Tables ["area"]. DefaultView;
This. ddl_area.DataTextField = "Name ";
This. ddl_area.DataValueField = "AreaID ";
This. ddl_area.DataBind ();
Cmd = new SqlCommand ("select * from Area", conn );
Cmd. CommandType = CommandType. Text;
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
Adapter. Fill (ds, "city ");
This. ddl_city.DataSource = ds. Tables ["city"]. DefaultView;
This. ddl_city.DataTextField = "Name ";
This. ddl_city.DataValueField = "AreaID ";
This. ddl_city.DataBind ();
}
Protected void ddl_area_SelectedIndexChanged (object sender, EventArgs e)
{
Conn = new SqlConnection (ConfigurationManager. ConnectionStrings ["strcon"]. ConnectionString );
This. ddl_city.Enabled = true;
String str1 = "0000 ";
Cmd = new SqlCommand ("select AreaID, Name from Area where substring (AreaID, 1, 2) = '" + this. ddl_area.SelectedValue.Substring (0000) + "'AND substring (AreaID,) <> '20160301' AND substring (AreaID,) = '00'", conn );
Cmd. CommandType = CommandType. Text;
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
DataSet ds = new DataSet ();
Adapter. Fill (ds, "city ");
This. ddl_city.DataSource = ds. Tables ["city"]. DefaultView;
This. ddl_city.DataTextField = "Name ";
This. ddl_city.DataValueField = "AreaID ";
This. ddl_city.DataBind ();
}
