SQL CHARINDEX function, InStr function, PATINDEX function, Stuff function

Source: Internet
Author: User
Tags numeric value

CHARINDEX function
Returns the starting position of a character or string within another string.
The CHARINDEX function call method is as follows:
CHARINDEX (expression1, expression2 [, Start_location])
Expression1 is the character to look for in expression2, Start_location is where the CHARINDEX function begins to find expression2 in expression1.
The CHARINDEX function returns an integer that is the position of the string to find in the string being searched for. If CHARINDEX does not find the string to find, then the function integer "0".

InStr function
Returns the position of the first occurrence of a string in another string.
The INSTR function call method is as follows:
INSTR ([Start,]string1, string2[, compare])
Parameters
Start
Options are available. A numeric expression that sets the starting position of each search. If omitted, the search begins at the position of the first character. If start contains Null, an error occurs. If you have specified compare, you must have the start parameter.
String1
Required option. A string expression that accepts the search.
string2
Required option. The string expression to search for.
Compare
Options are available. Indicates the numeric value of the comparison type to use when calculating substrings. See the Settings section for values. If omitted, a binary comparison is performed.

The nature of the two functions is as good as searching for whether a character appears in another string.

PATINDEX function
The PATINDEX function returns the starting position of a character or string in another string or expression, and the PATINDEX function supports the use of wildcards in the search string, which makes the PATINDEX function valuable for a changing search string.
Like the CHARINDEX function, the PATINDEX function returns the starting position of the search string in the searched string. If there is such a patindex function:
PATINDEX ('%bc% ', ' ABCD ')
The PATINDEX function returns a result of 2, which is the same as the CHARINDEX function. The% mark here tells the PATINDEX function to find the string "BC", regardless of the number of characters in the string being searched before and after "BC"!

Stuff function

Stuff function: Delete A string of the specified length and insert another set of characters at the specified starting point

STUFF (character_expression, start, length, character_expression)

Character_expression: The manipulated character,

Start: The starting point for deletion and insertion,

Length: deleted lengths,

Character_expression: The character to be inserted

Select Stuff ("abcdef", 2,3, "Ghijk")

Go

Results: Aghijk

-------------------------------------------------------------------------------------------

Function: Returns the position of the first occurrence of a pattern in a string

Patindex ('%pattern% ', expression)

Pattern: the mode to find

Expression: the string being searched for

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

Go

Results: 4

SQL CHARINDEX function, InStr function, PATINDEX function, Stuff function

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.