SQL truncation string

Substring
Returns a part of a character, binary, text, or image expression. For more information about valid Microsoft SQL server data types that can be used with this function, 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:
StringSubstr(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.

 

Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.163.com' Select left (@ S1, 4) ------------------------------------ Result: HTTP

 

B. truncates n characters from the right of the string (for exampleWww.163.com)

 

Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.163.com' Select right (@ S1, 11) ------------------------------------ Display result:Www.163.com

 

C. truncate any position and length of a string (for example, WWW)

 

Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.163.com' Select substring (@ S1, 8, 3) ------------------------------------ Display 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:

 

Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.163.com' Select substring (@ S1, charindex ('www ', @ S1) + 1, Len (@ S1 )) ------------------------------------ Display 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)

 

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 )) ------------------------------------ Display 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:

 

Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.163.com' Select Replace (@ S1, 'HTTP ://','') ------------------------------------ Display result:Www.163.com

 

Use the replace function to replace null characters except the strings to be displayed.

Method 4:

 

Declare @ S1 varchar (100) Select @ S1 = 'HTTP: // www.163.com' Select stuff (@ S1, charindex ('HTTP: // ', @ S1), Len ('HTTP ://'),'') ------------------------------------ Display 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:

 

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:

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 ();
}