SUBSTRING 
returns characters, binary, text , or part of an image expression. For valid microsoft® that can be used with this function sql server& For more information #8482; data types, see Data types.
Syntax
substring ( expression , start , length )
parameter
expression
is a string, binary string, text, image, column, or an expression that contains a column. Do not use an expression that contains an aggregate function.
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 return). substring ()
-Takes a substring of at any position;
Right ()
Right ()
LTrim ()
RTrim ()
-truncates the space without trim ().
charindex ()
Patindex ()
-the position of the wrote string in the parent string, not returning 0. Difference: PATINDEX supports wildcard characters, CHARINDEX not supported. Function Effect:
String intercept function, only single byte character use (for Chinese interception when the odd length is garbled, need to be processed separately), this function can intercept the string specified in the range of characters.
Application Range:
Title, content interception
function format:
String substr (string string, int start [, int length])
Parameter 1: Working with strings
Parameter 2: The starting position of the intercept (the first character is starting from 0)
Parameter 3: Number of characters intercepted
SUBSTR () More information can be found in the official PHP manual (string processing function library)
Example:
substr ("ABCDEFG", 0); return: ABCDEFG, intercept all characters
substr ("ABCDEFG", 2); return: CDEFG, intercept all characters after starting with C
substr ("ABCDEFG", 0, 3); return: ABC, intercept starting from a 3 characters
substr ("ABCDEFG", 0, 100); Return: abcdefg,100 The maximum number of preprocessed strings is returned, although it exceeds the length of the preprocessed string, but does not affect the return result.
substr ("ABCDEFG", 0,-3); Return: EFG, note parameter-3, negative indicates that the string is not changed from the beginning of the tail Example:
1. intercept functions with known lengths
A. Intercept starts from the left of the string n characters
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Left (@S1, 4)
------------------------------------
Displaying results: http |
B. Intercept starts from the right of the string n characters (for example, take character www.163.com)
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Right (@S1, 11)
------------------------------------
displaying results: www.163.com |
C. Intercepting arbitrary positions and lengths in a string (for example, fetching characters www)
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select SUBSTRING (@S1, 8, 3)
------------------------------------
displaying results: www.163.com |
The above examples are known as the location and length of the interception, the following is an example of unknown location
2. intercept functions in unknown locations
A. Intercepting a string after a specified string (for example, a string after the interception of http://)
Method One:
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)) */ ------------------------------------
displaying results: www.163.com |
Note: The CHARINDEX function is not case-sensitive when searching strings, so charindex (' www ', @S1) can also be written as charindex (' www ', @S1)
Method Two: (similar to method one)
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select Substring (@S1, PATINDEX ('%www% ', @S1) +1,len (@S1))
--This can also be written here: Select Substring (@S1, PATINDEX ('%//% ', @S1) +2,len (@S1))
------------------------------------
displaying results: www.163.com |
The difference between function Patindex and Charindex is that the former can parameter some parameters and increase the function of query. Method Three:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select REPLACE (@S1, '/http ', ')
------------------------------------
displaying results: www.163.com |
Replace the character with the character replacement function, replacing characters other than the display string with an empty Method Four:
Declare @S1 varchar (100)
Select @S1 = ' http://www.163.com '
Select STUFF (@S1, CHARINDEX (' http//', @S1), Len ('/http '), ')
------------------------------------
displaying results: www.163.com |
The difference between a function stuff and a replace is that the former can specify a replacement range, while the latter is a full range substitution
B. Intercepting a string after a specified character (for example, intercepting a file name in a C:\Windows\test.txt)
Unlike a, when the search object is not one, only the first position can be searched using the method above Method One:
Declare @S1 varchar (100)
Select @S1 = ' C:\Windows\test.txt '
Select Right (@S1, charindex (' \ ', REVERSE (@S1))-1)
-------------------------------------
displaying results: Text.txt |
Using the function reverse to get the length of the string to intercept
SUBSTR () Example: 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) = ' xx '", 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 ();
} |
