Tags: speed ref index Split Line Introduction method Fulltext SQL back If you have been looking for a more efficient method than the MySQL like statement, let me introduce you to a few. Like statementSELECT ' column ' from ' table ' where ' condition ' like '%keyword% ' In fact, you can useLocate (position) and InStrThese two functions take the place of One, locate statementsSELECT ' column ' from ' Table ' where locate (' keyword ', ' condition ') >0 second, or locate's name position Posit
In the project, we used the function of INSTR in Oracle, and learned this knowledge again and again carefully.In Oracle, you can use the INSTR function to judge a string to determine whether it contains the specified character.Its syntax is:INSTR (string, substring, position, occurrence)whichString: Represents the source string;SUBSTRING: Represents the substring to be found in the source string;Position: R
The format of the INSTR method isINSTR (source string, target string, start position, matching serial number)
For example, in INSTR ('upgrade FLOOR ', 'or', 3, 2), the source string is 'upgrade FLOOR', the target string is 'or', And the start position is 3, take the location of 2nd matching items.
The default search order is left to right. When the start position is negative, search from the right.
There
Repalce (STR_SOURCE,STR1,STR2) replaces the STR1 string in Str_source with the STR2 string,When str2 is null or ' ', the next function is the sameReplace (STR_SOURCE,STR1) removes the str1 string from the Str_sourceRegexp_replace (STR_SOURCE,PATTERN_STR) rejects the pattern_str string in Str_sourceThe difference between like and InStr in Oracle is that it is more efficient to InStr without creating an index
The instr syntax is as follows:Instr (string1, string2 [, start_position [, nth_appearance])
Function Description:This function returns the position of string2 in string1.
Start_positionThe parameter indicates the number of characters starting from string1 to find string2. This parameter is optional. If omitted, the default value is 1. The default search sequence of the instr function is from left to righ
The first thing that comes to mind is contains,contains usage as follows:
SELECT * from students where contains (address, ' Beijing ')
However, using the CONTAINS predicate has the condition that the column is indexed, that is, if the address column of the students table in the preceding statement does not have an index, an error is presented.
Fortunately, there is another way to use INSTR,INSTR:
visual| function 1.Split (a string expression that contains substrings and delimiters, [delimiter],[the number of substrings to be returned],[numeric value representing the comparison method to use when distinguishing substrings]), and [] is an optional part. This function returns a one-dimensional array with a zero subscript that contains the specified number of substrings.
Cases:
Dim xx As Variant
Xx=split ("Hello World", "") "divides hello World into Hello and world
text1.text= xx (0) ' Retur
Tags: Comment sel print SRC Technology COM-O SDN functionTest database: MySQL DatabaseINSTR (STR,SUBSTR) searches for the specified character (SUBSTR) in a string (STR) and returns the position (INDEX) of the specified character;STR is searched for stringSUBSTR the string you want to searchConclusion: In the string str, the first position (index) of the string substr appears, the index is calculated starting from 1, and if it is not found, it returns 0 without returning a negative number.--查询字符串
Label: --substr (string, intercept start position, intercept length) = return the intercepted word
SelectSubstr'miaoying',0,1) fromDual--The returned result is: M
SelectSubstr'miaoying',1,1) fromDual--The returned result is: m--description 0 and 1 both indicate that the intercept position is the first character
SelectSubstr'miaoying',-7,4) fromDual--The returned result is: iaoy--negative indicates that: 7 means starting from the right number seventh, that is, I, a string with a length of 4
--
One, InStr () function1. Syntax: InStr (Sourcestring,deststring,start,appearposition)Sourcestring represents the source string;Deststring represents the substring to be looked up from the source string;Start represents the starting position of the lookup, which is optional and defaults to 1;The Appearposition representative wants to find out the first occurrence of the deststring from the source character,
Tag: Specify the default Val floor post data type rate this heUsage of InStr, substr, and NVL in OracleINSTR Usage: INSTR (source string, the string to find, starting with the first few characters, to find the ordinal number of the first match) Returns the location found and returns 0 if it is not found. The default lookup order is left to right. When the starting position is negative, start looking from th
If you have been looking for a more efficient method than the MySQL like statement, let me introduce you to a few. Like statementSELECT ' column ' from ' table ' where ' condition ' like '%keyword% 'In fact, you can useLocate (position) and InStrThese two functions take the place ofLocate statementsSELECT ' column ' from ' Table ' where locate (' keyword ', ' condition ') >0or locate's name position.position StatementsSELECT ' column ' from ' table ' where position (' keyword ' in ' condition ')
Charindex FunctionReturns 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
In general, the following two methods are used to perform a fuzzy query on the name field of the tb table in the Oracle database:1. select * from tb where name like '% XX % ';2. select * from tb where instr (name, 'xx')> 0;
If no index is added to the name field, the efficiency is similar and there is basically no difference between the two.
To improve efficiency, we can add a non-unique index to the name field:Create index idx_tb_name on tb (name )
Label:Decode () corresponds to case-when function Case CHARINDEX ('/', start_point_name) When 0 then Start_point_name else substring (start_point_name,1,charindex ('/', start_point_name)-1) End Note: The syntax is different when SQL Server uses case to determine whether it is null Null should be judged by: Case when identifier was NULL then Airway_point_name else identifier end, The simplest way is to use the ISNLL function IsNull (idenfier, Airway_point_name) No way! IsNull (Idenfier, Airway_po
Common functions: Substr and InStr1.SUBSTR (String,start_position,[length]) to substring, return stringExplanation: String meta stringsStart_position start position (starting from 0)Length optional, number of substringsFor example:substr ("ABCDEFG", 0); return: ABCDEFG, intercept all characterssubstr ("ABCDEFG", 2); return: CDEFG, intercept all characters after starting with Csubstr ("ABCDEFG", 0, 3); return: ABC, intercept starting from a 3 characterssubstr ("ABCDEFG", 0, 100); Return: abcdefg,
InStr cannot use a variable in a parameter to create a function index to use the return value
When the table content has been set, the input value is fixed by the output value obtained by the function. Input value cannot be a variable
========================
How does the InStr function create a function index?
Create index index name on table name (InStr (ind
Only today can this function be used to find specific characters or strings.
The following is a simple example:
Dim content_text, searchword_text
'Content is the content and searchword is the text to be searched.Sub search (content, searchword)If instr (content, searchword)> 0 thenResponse. Write ("find ")ElseResponse. Write ("not found ")End ifEnd sub
Content_text = "Search for collectedCodeAnd tutorials are collected by Hui Nan at ordinary tim
Tags: end res string field Art Select RTP ABC negativefrom:http://1055592535.iteye.com/blog/1676235 In OracleYou can use the InStr function to judge a string to determine whether it contains the specified character.Finds the specified character in a string, returning the position of the specified character that was found.Grammar:InStr (Sourcestring,deststring,start,appearposition)InStr (' Source string ', '
Tags: style tar ext color HTTP CINSTR(Source string, target string, start position, match ordinal)In Oracle/plsql, the InStr function returns the position of the string to intercept in the source string. Retrieve only once, that is, from the beginning of the characterEnds at the end of the character.The syntax is as follows:InStr (string1, string2 [, Start_position [, Nth_appearance]])Parametric analysis:String1The source string to look up in this str
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.