SUBSTRING
Returns part of a character, binary, text, or an image expression. For more information about valid Microsoft®sql Server™ data types that can be used with this function, see Data types.
Grammar
SUBSTRING (expression, start, length)
Parameters
expression
is a string, binary string, text, image, column, or an expression that contains a column. Do not use an expression that contains aggregate functions.
start
Is an integer that specifies the starting position of the substring.
length
Is an integer that specifies the length of the substring (the number of characters or bytes to be returned).
substring ()
--Pick strings anywhere
Left ()
Right ()
--The left and right ends take the substring
LTrim ()
RTrim ()
--truncate space, no trim ().
CHARINDEX ()
Patindex ()
--the position of the substring in the parent string, not returning 0. Difference: PATINDEX supports wildcard characters, CHARINDEX not supported.
function Effect:
String intercept functions, only single-byte characters used (for Chinese interception when the odd length will appear garbled, need to be processed separately), this function can intercept the string specified in the range of characters.
Application scope:
Title, content interception
function Format:
String substr (string string, int start [, int length])
Parameter 1: Processing strings
Parameter 2: The starting position of the intercept (the first character is starting at 0)
Parameter 3: Number of characters intercepted
SUBSTR () More information can be queried in the official PHP manual (string processing function library)
For Example:
substr ("ABCDEFG", 0); Back: ABCDEFG, intercepting all characters
substr ("ABCDEFG", 2); Back: CDEFG, intercept all characters after starting C
substr ("ABCDEFG", 0, 3); Back: ABC, intercept 3 characters starting from a
substr ("ABCDEFG", 0, 100); Return: abcdefg,100 Although the maximum length of the preprocessed string is exceeded, it will not affect the return result, and the system is returned at the highest number of preprocessing strings.
substr ("ABCDEFG", 0,-3); Back: EFG, note that parameter-3, when negative, indicates that the string arrangement position is unchanged from the tail start
Example:
1. Intercepting functions of known length
A. Intercept start n characters from the left side of the string
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Left (@S1, 4)
------------------------------------
Display results: http
B. Intercepting n characters starting from the right of the string (for example, character www.163.com)
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Right (@S1, 11)
------------------------------------
Display results: www.163.com
C. Intercept arbitrary position and length in a string (e.g., character www)
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select SUBSTRING (@S1, 8, 3)
------------------------------------
Display results: www.163.com
All of the above examples are known to intercept locations and lengths, and here are examples of unknown locations
2. function to intercept unknown position
A. Intercepting the string after the specified string (for example, intercepting the string following the http://)
Method One:
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Substring (@S1, CHARINDEX (' www ', @S1) +1,len (@S1))
* This can also be written here: Select Substring (@S1, CHARINDEX ('//', @S1) +2,len (@S1)) * *
------------------------------------
Display results: www.163.com
Note: The CHARINDEX function searches for strings that are case-insensitive, so charindex (' www ', @S1) can also be written as charindex (' www ', @S1)
Method Two: (similar to the method one)
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Substring (@S1, PATINDEX ('%www% ', @S1) +1,len (@S1))
It can also be written here: Select Substring (@S1, PATINDEX ('%//% ', @S1) +2,len (@S1))
------------------------------------
Display results: www.163.com
The difference between function Patindex and Charindex is that the former can parameter some parameters and increase the function of the query.
Method Three:
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select REPLACE (@S1, ' http://', ')
------------------------------------
Display results: www.163.com
Replace the character with a character substitution, replacing the characters that need to display the string with NULL
Method Four:
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select STUFF (@S1, CHARINDEX (' http://', @S1), Len (' http://'), "
------------------------------------
Display results: www.163.com
The difference between function stuff and replace is that the former can specify a replacement range, while the latter is full range substitution
B. Intercepting the string after the specified character (for example, intercepting the filename in C:\Windows\test.txt)
Unlike a, when a search object is not one, you can use the above method to search only the first location
Method One:
Copy Code code as follows:
Declare @S1 varchar (100)
Select @S1 = ' C:\Windows\test.txt '
Select Right (@S1, charindex (' \ ', REVERSE (@S1))-1)
-------------------------------------
Display results: Text.txt
Using function reverse to get the length of the string to intercept
SUBSTR ()
Example:
Copy Code code 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 the 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 (0,2) + "' and Substring (areaid,3,4) <> ' 0000 ' and Substring (areaid,5,2) = '", 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 ();
}
