Charindex function, instr function, patindex function, stuff Function

 

Charindex Function
Returns the starting position of a character or string in another string.
The charindex function is called as follows:
Charindex (expression1, expression2 [, start_location])

Expression1 is the character to be searched in expression2. start_location is the position where the charindex function starts to find expression1 in expression2.
The charindex function returns an integer that is the position of the string to be searched in the string to be searched. If charindex does not find the string to be searched, the function integer is "0 ".

Instr Functions
Returns the position where a string appears for the first time in another string.
The instr function is called as follows:
Instr ([start,] string1, string2 [, compare])
Parameters
Start
Optional. Value expression, used to set the start position of each search. If omitted, the search starts from the first character. If start contains null, an error occurs. If the compare parameter is specified, the start parameter is required.
String1
Required. String expression to be searched.
String2
Required. The string expression to be searched.
Compare
Optional. Indicates the value of the comparison type used to calculate the substring. For values, see the "Settings" section. If omitted, binary comparison is executed.

The properties of the two functions are very similar. It is used to search for a character in another string.

Patindex Function
The patindex function returns the starting position of a character or string in another string or expression. The patindex function supports searching strings with wildcards, which makes the patindex function very valuable for changing search strings.
Like the charindex function, the patindex function returns the start position of the search string in the searched string. Suppose there is such a patindex function:
Patindex ('% BC %', 'abc ')
The result returned by the patindex function is 2, which is the same as that returned by the charindex function. Here, the % Mark tells the patindex function to find the string "BC", regardless of the number of characters in the searched string before and after "BC!

Stuff function: delete a string of the specified length and insert another group of characters at the specified start point.

Stuff (character_expression, start, length, character_expression)

Character_expression: Operator character,

Start: the start point of deletion and insertion,

Length: delete length,

Character_expression: the character to be inserted.

Select stuff ("abcdef", 2, 3, "ghijk ")

Go

Result: aghijk

Bytes -------------------------------------------------------------------------------------------

Function: Position of the first occurrence of the return mode in the string.

Patindex ('% Pattern %', Expression
)

Pattern: The pattern to be searched

Expression: the string to be searched.

Select patindex ('% jinweida %', '123jinweida54 ')

Go

Result: 4