Vba instr
Содержание:
- SQL Учебник
- SQL Справочник
- InstrRev Function
- VBA Coding Made Easy
- UPDATE несколько записей
- Пример PRAGMA UDF
- Parameters
- Practice Exercise #1:
- Practice Exercise #2:
- Oracle INSTR Function Syntax and Parameters
- SQL Учебник
- Oracle Query Types
- Синтаксис, параметры, значения
- INSTR Function
- SQL References
- SQL Учебник
- Example — return_option parameter
- Example — Match on more than one alternative
- Примеры использования в VBA Excel
- Примечание
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
SQL Справочник
SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE
MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION
SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL ОператорыSQL Типы данныхSQL Краткий справочник
InstrRev Function
The Instr Function searches from the left. Instead you can search from the right using the InstrRev Function. The InstrRev Function works very similarly to the Instr function.
|
1 |
SubFindSomeText_FromRight() MsgBox InStrRev(«Look in this string»,»Look») EndSub |
Just like the Instr function this will return 1 because there is only one instance of “Look” in the text. But if we add a second “Look”, you’ll see that it returns the position of the right-most “Look”:
|
1 |
SubFindSomeText_FromRight() MsgBox InStrRev(«Look in this string Look»,»Look») EndSub |
Next we will review more Instr examples.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
If String Contains Substring
Here we will use an If statement to test if a string contains a a substring of text:
|
1 |
PublicSubFindSomeText() IfInStr(«Look in this string»,»look»)=Then MsgBox»No match» Else MsgBox»At least one match» EndIf EndSub |
Find Text String in a Cell
You can also find a string in a cell:
|
1 |
SubFind_String_Cell() IfInStr(Range(«B2″).Value,»Dr.»)>Then Range(«C2″).Value=»Doctor» EndIf EndSub |
Or loop through a range of cells to test if the cells contain some text:
|
1 |
SubSearch_Range_For_Text() Dimcell AsRange ForEachcell InRange(«b2:b6») IfInStr(cell.Value,»Dr.»)>Then cell.Offset(,1).Value=»Doctor» EndIf Nextcell EndSub |
Find Position of a Character in a String
This code will find the position of a single character in a string and assign the position to a variable:
|
1 |
SubFind_Char() DimnAsLong n=InStr(«Here Look Here»,»L») EndSub |
Search String for Word
This code will search a string for a word:
|
1 |
SubSearch_String_For_Word() DimnAsLong n=InStr(«Here Look Here»,»Look») Ifn=Then MsgBox»Word not found» Else MsgBox»Word found in position: «&n EndIf EndSub |
If Variable Contains String
This code will test if a string variable contains a string of text:
|
1 |
SubVariable_Contains_String() Dimstr AsString str=»Look Here» IfInStr(str,»Here»)>Then MsgBox»Here found!» EndIf EndSub |
Instr and the Left Function
Instr can be used along with other text functions like Left, Right, Len, and Mid to trim text.
With the Left function you can output the text prior to a string of text:
|
1 |
SubInstr_Left() Dimstr AsString DimnAsLong str=»Look Here» n=InStr(str,»Here») MsgBox Left(str,n-1) EndSub |
To learn more, read our article: VBA text functions
UPDATE несколько записей
Именно предложение WHERE определяет, сколько записей будет обновлено.
Следующая инструкция SQL обновит имя контакта до «Juan» для всех записей, где страна — «Mexico»:
Пример
UPDATE Customers
SET ContactName=’Juan’
WHERE Country=’Mexico’;
Выбор из таблицы «Customers» теперь будет выглядеть следующим образом:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Alfred Schmidt | Obere Str. 57 | Frankfurt | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Juan | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Juan | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
Пример PRAGMA UDF
В ряде презентаций, предшествовавших официальному выпуску 12c, выступавшие упоминали PRAGMA UDF(User Defined Function), которая предположительно дает вам преимущества производительности встроенного PL/SQL, в то же время позволяя вам определять объект PL/SQL вне оператора SQL. Следующий код переопределяет предыдущую обычную функцию для использования этой прагмы.
Oracle PL/SQL
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
PRAGMA UDF;
BEGIN
RETURN p_id;
END;
|
1 2 3 4 5 |
CREATEORREPLACEFUNCTIONnormal_function(p_idINNUMBER)RETURNNUMBERIS
PRAGMAUDF; RETURNp_id; END; |
Как только функция скомпилирована, выполнение теста из предыдущего раздела для этой функции дает довольно интересные результаты.
Oracle PL/SQL
SET SERVEROUTPUT ON
DECLARE
l_time PLS_INTEGER;
l_cpu PLS_INTEGER;
l_sql VARCHAR2(32767);
l_cursor SYS_REFCURSOR;
TYPE t_tab IS TABLE OF NUMBER;
l_tab t_tab;
BEGIN
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;
l_sql := ‘WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1’;
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line(‘WITH_FUNCTION : ‘ ||
‘Time=’ || TO_CHAR(DBMS_UTILITY.get_time — l_time) || ‘ hsecs ‘ ||
‘CPU Time=’ || (DBMS_UTILITY.get_cpu_time — l_cpu) || ‘ hsecs ‘);
l_time := DBMS_UTILITY.get_time;
l_cpu := DBMS_UTILITY.get_cpu_time;
l_sql := ‘SELECT normal_function(id)
FROM t1’;
OPEN l_cursor FOR l_sql;
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line(‘NORMAL_FUNCTION: ‘ ||
‘Time=’ || TO_CHAR(DBMS_UTILITY.get_time — l_time) || ‘ hsecs ‘ ||
‘CPU Time=’ || (DBMS_UTILITY.get_cpu_time — l_cpu) || ‘ hsecs ‘);
END;
WITH_FUNCTION : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs
PL/SQL procedure successfully completed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
SETSERVEROUTPUTON DECLARE l_timePLS_INTEGER; l_cpuPLS_INTEGER; l_sqlVARCHAR2(32767); l_cursorSYS_REFCURSOR; TYPEt_tabISTABLEOFNUMBER; l_tabt_tab; l_time:=DBMS_UTILITY.get_time; l_cpu:=DBMS_UTILITY.get_cpu_time; l_sql:=’WITH FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS FROM t1′; OPENl_cursorFORl_sql; FETCHl_cursor BULKCOLLECTINTOl_tab; CLOSEl_cursor; DBMS_OUTPUT.put_line(‘WITH_FUNCTION : ‘|| ‘Time=’||TO_CHAR(DBMS_UTILITY.get_time-l_time)||’ hsecs ‘|| ‘CPU Time=’||(DBMS_UTILITY.get_cpu_time-l_cpu)||’ hsecs ‘); l_time:=DBMS_UTILITY.get_time; l_cpu:=DBMS_UTILITY.get_cpu_time; l_sql:=’SELECT normal_function(id) FROM t1′; OPENl_cursorFORl_sql; FETCHl_cursor BULKCOLLECTINTOl_tab; CLOSEl_cursor; DBMS_OUTPUT.put_line(‘NORMAL_FUNCTION: ‘|| ‘Time=’||TO_CHAR(DBMS_UTILITY.get_time-l_time)||’ hsecs ‘|| ‘CPU Time=’||(DBMS_UTILITY.get_cpu_time-l_cpu)||’ hsecs ‘);
END; |
Кажется, что автономная функция, использующая PRAGMA UDF, последовательно выполняет встроенную функцию.
У меня сложилось впечатление, что вызов функции, определенной с помощью PRAGMA UDF напрямую из PL / SQL, не удастся. Это не похоже на случайность.
Oracle PL/SQL
DECLARE
l_number NUMBER;
BEGIN
l_number := normal_function(1);
END;
PL/SQL procedure successfully completed.
|
1 2 3 4 5 6 7 |
DECLARE l_numberNUMBER; l_number:=normal_function(1); END; |
Parameters
string |
(CHAR or VARCHAR, or BINARY or VARBINARY) Text expression to search. |
substring |
(CHAR or VARCHAR, or BINARY or VARBINARY) String to search for. |
position |
Nonzero integer indicating the character of string where Vertica begins the search. If position is negative, then Vertica counts backward from the end of string and then searches backward from the resulting position. The first character of string occupies the default position 1, and position cannot be 0. |
occurrence |
Integer indicating which occurrence of string Vertica searches. The value of occurrence must be positive (greater than 0), and the default is 1. |
Practice Exercise #1:
Based on the contacts table, insert a contact record whose contact_id is 1000, last_name is Smith, first_name is Jane, and address is 10 Somewhere St.:
CREATE TABLE contacts ( contact_id number(10) not null, last_name varchar2(50) not null, first_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(20), zip_code varchar2(10), CONSTRAINT contacts_pk PRIMARY KEY (contact_id) );
Solution for Practice Exercise #1:
The following Oracle INSERT statement would insert this record into the employees table:
INSERT INTO contacts (contact_id, last_name, first_name, address) VALUES (1000, 'Smith', 'Jane', '10 Somewhere St.');
Practice Exercise #2:
Based on the contacts and customers table, insert into the contacts table all customers who reside in the state of ‘Florida’.
CREATE TABLE contacts ( contact_id number(10) not null, last_name varchar2(50) not null, first_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(20), zip_code varchar2(10), CONSTRAINT contacts_pk PRIMARY KEY (contact_id) ); CREATE TABLE customers ( customer_id number(10) not null, last_name varchar2(50) not null, first_name varchar2(50) not null, address varchar2(50), city varchar2(50), state varchar2(20), zip_code varchar2(10), CONSTRAINT customers_pk PRIMARY KEY (customer_id) );
Solution for Practice Exercise #2:
The following Oracle INSERT statement would insert this record into the suppliers table:
INSERT INTO contacts (contact_id, last_name, first_name, address, city, state, zip_code) SELECT customer_id, last_name, first_name, address, city, state, zip_code FROM customers WHERE state = 'Florida';
Since the number of fields in the contacts and customers table are the same and the fields are listed in the same order, you could write the solution as follows (though it is generally better practice to list the column names in case the table definitions change):
INSERT INTO contacts SELECT * FROM customers WHERE state = 'Florida';
Oracle INSTR Function Syntax and Parameters
The syntax for the INSTR function is:
This Oracle INSTR function is actually different from other functions in that it has several different versions. Each of them vary in how they determine the position of the substring to return:
- INSTR calculates the length using characters defined by the input character set.
- INSTRB calculates the length using bytes.
- INSTRC calculates the length using Unicode complete characters.
- INSTR2 calculates the lengths using USC2 code points.
- INSTR4 calculates the lengths using USC4 code points.
The parameters of the INSTR function and its variants are:
- string (mandatory): This is the text string that is searched in. It is usually the larger of the two strings. If you are looking to “find X within Y”, this is the Y component.
- substring (mandatory): This is the text string that is searched for. It is usually the smaller of the two strings. If you are looking to “find X within Y”, this is the X component.
- start_position (optional): This is a nonzero integer, and indicates where in the string value to start the search. The default is 1, which is the start of the string. If it is negative, the function searches and counts from the end of string and goes backwards.
- occurrence (optional): This is a positive integer, and indicates which occurrence of the substring the function should search for. The default value is 1, which means the functions searches for the first occurrence.
The search performed in this function is case-sensitive.
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
Oracle Query Types
| SELECT Statement | Retrieve records from a table |
| INSERT Statement | Insert records into a table |
| INSERT ALL Statement | Insert multiple records into one or more tables |
| UPDATE Statement | Update records in a table |
| DELETE Statement | Delete records from a table |
| TRUNCATE TABLE Statement | Delete all records from a table (no rollback) |
| UNION Operator | Combine 2 result sets (removes duplicates) |
| UNION ALL Operator | Combine 2 result sets (includes duplicates) |
| INTERSECT Operator | Intersection of 2 result sets |
| MINUS Operator | Result set of one minus the result set of another |
| Subqueries | A query within a query |
| PIVOT Clause | Create a cross-tabulation query |
Синтаксис, параметры, значения
Синтаксис функции InStr
Полный вариант:
Сокращенный вариант:
Чаще всего в VBA Excel используется сокращенный вариант функции со значениями необязательных параметров по умолчанию.
Параметры функции InStr
| Параметр | Описание | Значение по умолчанию |
| начало | Необязательный аргумент.* Числовое выражение, которое задает начальную позицию для поиска. | 1 |
| строка1 | Обязательный аргумент. Строковое выражение, в котором выполняется поиск. | – |
| строка2 | Обязательный аргумент. Искомое строковое выражение. | – |
| сравнение | Необязательный аргумент. Задает тип сравнения строк. | –1** |
* Если задан аргумент сравнение, аргумент начало является обязательным.
** Если аргумент сравнение не указан, используется значение инструкции Option Compare, заданное на уровне модуля. Если инструкция Option Compare в начале модуля отсутствует, используется ее значение по умолчанию – 0 (двоичное сравнение).
Если параметр начало или параметр сравнение содержит значение NULL, возникает ошибка.
Значения аргумента «сравнение»
| Константа | Значение | Описание |
| vbUseCompareOption | -1 | Сравнение с помощью параметра инструкции Option Compare. |
| vbBinaryCompare | Двоичное (бинарное) сравнение.* | |
| vbTextCompare | 1 | Текстовое сравнение.* |
| vbDatabaseCompare | 2 | Сравнение на основе сведений из базы данных. Только для Microsoft Access. |
* При двоичном сравнении учитывается регистр букв, при текстовом – не учитывается.
Значения функции InStr
| Если | Возвращаемое значение |
| строка2 найдена в строке1 | Позиция первого найденного соответствия. |
| строка2 не найдена в строке1 | |
| строка2 является пустой | Значение аргумента начало. |
| строка2 равна NULL | NULL |
| строка1 является пустой | |
| строка1 равна NULL | NULL |
| начало > длины строка1 |
INSTR Function
The VBA Instr Function checks if a string of text is found in another string of text. It returns 0 if the text is not found. Otherwise it returns the character position where the text is found.
The Instr Function performs exact matches. The VBA Like Operator can be used instead to perform inexact matches / pattern matching by using Wildcards.
Instr Example
The following code snippet searches the string “Look in this string” for the word “Look”. The Instr Function returns 1 because the text is found in the first position.
|
1 |
SubFindSomeText() MsgBox InStr(«Look in this string»,»Look») EndSub |
This second example returns 7 because the text is found starting in the 7th position:
|
1 |
SubFindSomeText2() MsgBox InStr(«Don’t Look in this string»,»Look») EndSub |
Important! The Instr Function is case-sensitive by default. This means “look” will not match with “Look”. To make the test case-insensitive read below.
Instr Syntax
The syntax for the Instr function is as follows:
| 1 | Instr(start,string,substring,compare) |
(optional) – This optional argument is the starting position of the search. Enter 1 to start searching from position 1 (or leave blank). Enter 5 to start searching from position 5. Important! The INSTR function calculates the character position by counting from 1 NOT from the position.
string – The string of text to search in.
substring – The string of text to find in the primary string.
(optional) – By default, Instr is case-sensitive. By setting this argument you can make Instr Case insensitive:
|
Argument vb Value |
Argument Integer | Description |
| vbBinaryCompare | (Default) Case-sensitive | |
|
vbTextCompare |
1 |
Not Case-sensitive |
|
vbDatabaseCompare |
2 |
MS Access Only. Uses information in the database to perform comparison. |
Instr Start Position
The Instr start position allows you to indicate the character position where you will begin your search. Keep in mind however, the Instr output will always count from 1.
Here we set the start position to 3 to skip the first B:
|
1 |
SubInstr_StartPosition() MsgBox InStr(3,»ABC ABC»,»B») EndSub |
The result is 6 because the second B is the 6th character in the string.
Case-Insensitive INSTR Test
By default, VBA treats “L” different from “l”. In other words, VBA is case-sensitive. This is true of all text functions. To make VBA case-insensitive, set the argument to 1 or vbTextCompare.
|
1 |
PublicSubFindText_IgnoreCase() MsgBox InStr(1,»Don’t Look in this string»,»look»,vbTextCompare) EndSub |
Alternatively, you can add Option Compare Text to the top of your code module:
| 1 | OptionCompare Text |
|
1 |
OptionCompare Text PublicSubFindText_IgnoreCase2() MsgBox InStr(«Don’t Look in this string»,»look») EndSub |
Option Compare Text will impact all of the code in that module. I personally place this at the top of any module that deals with text because I never care about case differences.
SQL References
SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE
MySQL Functions
String Functions
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Date Functions
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION
SQL Server Functions
String Functions
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL Quick Ref
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
Example — return_option parameter
Finally, let’s look at how the return_option parameter affects our results.
For example:
SELECT REGEXP_INSTR ('TechOnTheNet', 'The', 1, 1, 0, 'i')
FROM dual;
Result: 7
In this basic example, we are searching for a pattern in a string and the search is case-insensitive. We have specified the return_option parameter as 0 which means the position of the first character of the pattern will be returned.
Now, let’s change the return_option parameter to 1 and see what happens.
For example:
SELECT REGEXP_INSTR ('TechOnTheNet', 'The', 1, 1, 1, 'i')
FROM dual;
Result: 10
A return_option parameter of 1 tells the REGEXP_INSTR function to return the position of the character following the matched pattern. In this example, the function will return 10.
Example — Match on more than one alternative
The next example that we will look at involves using the pattern. The pattern is used like an «OR» to specify more than one alternative.
For example:
SELECT REGEXP_INSTR ('Anderson', 'a|e|i|o|u')
FROM dual;
Result: 4
This example will return 4 because it is searching for the first vowel (a, e, i, o, or u) in the string. Since we did not specify a match_parameter value, the REGEXP_INSTR function will perform a case-sensitive search which means that the ‘A’ in ‘Anderson’ will not be matched.
We could modify our query as follows to perform a case-insensitive search as follows:
SELECT REGEXP_INSTR ('Anderson', 'a|e|i|o|u', 1, 1, 0, 'i')
FROM dual;
Result: 1
Now because we have provide a match_parameter of ‘i’, the query will return 1 as the result. This time, the ‘A’ in ‘Anderson’ will be found as a match.
Now, let’s quickly show how you would use this function with a column.
So let’s say we have a contact table with the following data:
| contact_id | last_name |
|---|---|
| 1000 | Anderson |
| 2000 | Smith |
| 3000 | Johnson |
Now, let’s run the following query:
SELECT contact_id, last_name, REGEXP_INSTR (last_name, 'a|e|i|o|u', 1, 1, 0, 'i') AS first_occurrence FROM contacts;
These are the results that would be returned by the query:
Примеры использования в VBA Excel
Пример 1
Самый простой пример:
|
1 |
SubTest1() DimxAsVariant x=InStr(«На горе Фернандо-По, где гуляет Гиппо-по»,»Фернандо») MsgBoxx ‘Здесь x будет равен 9 EndSub |
Пример 2
В этом примере, используя одинаковые строки, в которых выполняется поиск, и искомые подстроки, применим разные виды сравнения – двоичное (бинарное) и текстовое, и посмотрим на результаты.
|
1 |
SubTest2() DimxAsVariant x=InStr(10,»На горе Фернандо-По, где гуляет Гиппо-по»,»по»,) MsgBoxx ‘Здесь x будет равен 36 (поиск с учетом регистра символов) x=InStr(10,»На горе Фернандо-По, где гуляет Гиппо-по»,»по»,1) MsgBoxx ‘Здесь x будет равен 18 (поиск без учета регистра символов) EndSub |
Обратите внимание: несмотря на то, что начало поиска мы задали с 10 символа, номер позиции первого вхождения считается с начала строки, в которой выполняется поиск
Пример 3
В этом примере посмотрим на результаты посимвольного и побайтового сравнения, опять же используя одинаковые строки и искомые подстроки.
|
1 |
SubTest3() DimxAsVariant x=InStr(«На горе Фернандо-По, где гуляет Гиппо-по»,»гор») MsgBoxx ‘Здесь x будет равен 4 x=InStrB(«На горе Фернандо-По, где гуляет Гиппо-по»,»гор») MsgBoxx ‘Здесь x будет равен 7 EndSub |
Результат 7 при побайтовом сравнении получен для кодировки, у которой один символ составляет 2 байта.
Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.
Примечание
Допустимые параметры для пространства имен ‘USERENV’: (Обратите внимание, что не все параметры действительны во всех версиях Oracle)
| Parameter | Explanation | Oracle 9i | Oracle 10g | Oracle 11g |
|---|---|---|---|---|
| ACTION | Returns the position in the module | No | Yes | Yes |
| AUDITED_CURSORID | Returns the cursor ID of the SQL that triggered the audit | Yes | Yes | Yes |
| AUTHENTICATED_IDENTITY | Returns the identity used in authentication | No | Yes | Yes |
| AUTHENTICATION_DATA | Authentication data | Yes | Yes | Yes |
| AUTHENTICATION_METHOD | Returns the method of authentication | No | Yes | Yes |
| AUTHENTICATION_TYPE | Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy | Yes | No | No |
| BG_JOB_ID | If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL. | Yes | Yes | Yes |
| CLIENT_IDENTIFIER | Returns the client identifier (global context) | Yes | Yes | Yes |
| CLIENT_INFO | User session information | Yes | Yes | Yes |
| CURRENT_BIND | Bind variables for fine-grained auditing | No | Yes | Yes |
| CURRENT_SCHEMA | Returns the default schema used in the current schema | Yes | Yes | Yes |
| CURRENT_SCHEMAID | Returns the identifier of the default schema used in the current schema | Yes | Yes | Yes |
| CURRENT_SQL | Returns the SQL that triggered the audit event | Yes | Yes | Yes |
| CURRENT_SQL_LENGTH | Returns the length of the current SQL statement that triggered the audit event | No | Yes | Yes |
| CURRENT_USER | Name of the current user | Yes | No | No |
| CURRENT_USERID | Userid of the current user | Yes | No | No |
| DB_DOMAIN | Domain of the database from the DB_DOMAIN initialization parameter | Yes | Yes | Yes |
| DB_NAME | Name of the database from the DB_NAME initialization parameter | Yes | Yes | Yes |
| DB_UNIQUE_NAME | Name of the database from the DB_UNIQUE_NAME initialization parameter | No | Yes | Yes |
| ENTRYID | Available auditing entry identifier | Yes | Yes | Yes |
| ENTERPRISE_IDENTITY | Returns the user’s enterprise-wide identity | No | Yes | Yes |
| EXTERNAL_NAME | External of the database user | Yes | No | No |
| FG_JOB_ID | If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL. | Yes | Yes | Yes |
| GLOBAL_CONTEXT_MEMORY | The number used in the System Global Area by the globally accessed context | Yes | Yes | Yes |
| GLOBAL_UID | The global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins. | No | No | Yes |
| HOST | Name of the host machine from which the client has connected | Yes | Yes | Yes |
| IDENTIFICATION_TYPE | Returns the way the user’s schema was created | No | Yes | Yes |
| INSTANCE | The identifier number of the current instance | Yes | Yes | Yes |
| INSTANCE_NAME | The name of the current instance | No | Yes | Yes |
| IP_ADDRESS | IP address of the machine from which the client has connected | Yes | Yes | Yes |
| ISDBA | Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE. | Yes | Yes | Yes |
| LANG | The ISO abbreviate for the language | Yes | Yes | Yes |
| LANGUAGE | The language, territory, and character of the session. In the following format: language_territory.characterset | Yes | Yes | Yes |
| MODULE | Returns the appplication name set through DBMS_APPLICATION_INFO package or OCI | No | Yes | Yes |
| NETWORK_PROTOCOL | Network protocol used | Yes | Yes | Yes |
| NLS_CALENDAR | The calendar of the current session | Yes | Yes | Yes |
| NLS_CURRENCY | The currency of the current session | Yes | Yes | Yes |
| NLS_DATE_FORMAT | The date format for the current session | Yes | Yes | Yes |
| NLS_DATE_LANGUAGE | The language used for dates | Yes | Yes | Yes |
| NLS_SORT | BINARY or the linguistic sort basis | Yes | Yes | Yes |
| NLS_TERRITORY | The territory of the current session | Yes | Yes | Yes |
| OS_USER | The OS username for the user logged in | Yes | Yes | Yes |
| POLICY_INVOKER | The invoker of row-level security policy functions | No | Yes | Yes |
| PROXY_ENTERPRISE_IDENTITY | The Oracle Internet Directory DN when the proxy user is an enterprise user | No | Yes | Yes |
| PROXY_GLOBAL_UID | The global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users. | No | Yes | Yes |
| PROXY_USER | The name of the user who opened the current session on behalf of SESSION_USER | Yes | Yes | Yes |
| PROXY_USERID | The identifier of the user who opened the current session on behalf of SESSION_USER | Yes | Yes | Yes |
| SERVER_HOST | The host name of the machine where the instance is running | No | Yes | Yes |
| SERVICE_NAME | The name of the service that the session is connected to | No | Yes | Yes |
| SESSION_USER | The database user name of the user logged in | Yes | Yes | Yes |
| SESSION_USERID | The database identifier of the user logged in | Yes | Yes | Yes |
| SESSIONID | The identifier of the auditing session | Yes | Yes | Yes |
| SID | Session number | No | Yes | Yes |
| STATEMENTID | The auditing statement identifier | No | Yes | Yes |
| TERMINAL | The OS identifier of the current session | Yes | Yes | Yes |
