Ms sql charindex: CHARINDEX (Transact-SQL) — SQL Server

Парсинг сайта средствами языка T-SQL / Хабр

Добрый день! Пару лет назад почитал форумы в интернете и подготовил прямой запрос на языке T-SQL получающий координаты GPS из адресов доставки всех клиентов компании в базе «1С: Управление торговлей, Версия 11». Вызван переход на работу по координатам был частыми случаями изменения адресного классификатора в разных программах отделов (отдела продаж и отдела доставки). Уговоры отделов работать в одной программе пока без успешны.

Шаг первый:


— Получить координаты одного клиента из адреса его доставки (для отладки шаблона):

Declare @URI1 nvarchar(4000)='107113, Москва г, Поперечный просек, дом № 1-Г'
DECLARE @count int, @i1 int, @urlReturn nvarchar(4000)
Declare @s1 char
SET @count = LEN(@URI1)
SET @i1 = 1
SET @urlReturn = ''
while (@i1 <= @count)
begin 
  select @s1 = SUBSTRING(@URI1, @i1, 1)
  if (ASCII(@s1) > 191 AND ASCII(@s1) <= 256)
  begin
    select @urlReturn = @urlReturn + sys. fn_varbintohexstr(CAST(@s1 AS VARBINARY(MAX)))
    select @urlReturn = replace(@urlReturn, '0x', '%')
  end
  else
    select @urlReturn = @urlReturn + @s1
  set @i1 = @i1 +1
end
DECLARE @URI varchar(2000)='https://geocode-maps.yandex.ru/1.x/?geocode='+@urlReturn, @methodName varchar(50)='GET'
DECLARE @objectID int, @hResult int
EXEC 	@hResult = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objectID OUT
IF @hResult <> 0 goto destroy
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'
IF @hResult <> 0 goto destroy
EXEC 	@hResult = sp_OAMethod @objectID, 'send', null
IF @hResult <> 0 goto destroy
DECLARE @t TABLE(s nvarchar(max))
INSERT @t
	EXEC sp_OAGetProperty @objectID, 'responseText'
IF @hResult <> 0 goto destroy
		DECLARE @n int
		DECLARE @STRLEN int
		SELECT @STRLEN=LEN(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5))+1 FROM @t
		SELECT @n=CHARINDEX(' ',SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5)) FROM @t
		SELECT 
			SUBSTRING(s,CHARINDEX('<pos>',s)+5,@n) AS LON,
			SUBSTRING(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5),@n,@STRLEN-@n) AS LAT,
			SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5) AS MAP
		FROM @t
destroy:
	exec sp_OADestroy @objectID

Шаг второй:


— Перебор всех адресов доставки клиентов и получение координат GPS:

DECLARE @URI1 AS nvarchar(4000)
DECLARE @ID1 AS nvarchar(11)
DECLARE curMarks CURSOR
    LOCAL SCROLL STATIC
    FOR
		SELECT
			[ki]. _Fld2260 AS [Adress],
			[p].[_CODE]
		FROM [UT_TEST_COPY].[dbo].[_Reference107] as [p]
			LEFT OUTER JOIN [UT_TEST_COPY].[dbo].[_Reference107_VT2256] as [ki] WITH (NOLOCK) ON ([p].[_IDRRef]=[ki].[_Reference107_IDRRef]) 
		WHERE
			[ki]._Fld2259RRef=0x8757A30F90F658984F74B3E6BDCE0041
			AND [p]._Fld11004RRef=0xA576BCAEC54B2C9E11E23ACC96E85A13
			/*AND [p]._Fld11721RRef=0x814665286A763EC746207B8AD89C8693*/
			AND [p]._Fld11721RRef=0xB54E694250E409A6463884A95998E32A
    OPEN curMarks
    FETCH NEXT FROM curMarks
    INTO @URI1,@ID1;        
    WHILE @@FETCH_STATUS = 0
    BEGIN
		DECLARE @count int, @i1 int, @urlReturn nvarchar(4000)
		Declare @s1 char
		SET @count = LEN(@URI1)
		SET @i1 = 1
		SET @urlReturn = ''
		while (@i1 <= @count)
		begin 
		  select @s1 = SUBSTRING(@URI1, @i1, 1)
		  if (ASCII(@s1) > 191 AND ASCII(@s1) <= 256)
		  begin
			select @urlReturn = @urlReturn + sys.fn_varbintohexstr(CAST(@s1 AS VARBINARY(MAX)))
			select @urlReturn = replace(@urlReturn, '0x', '%')
		  end
		  else
			select @urlReturn = @urlReturn + @s1
		  set @i1 = @i1 +1
		end
		DECLARE @URI varchar(2000)='https://geocode-maps. yandex.ru/1.x/?geocode='+@urlReturn, @methodName varchar(50)='GET'
		DECLARE @objectID int, @hResult int
		EXEC 	@hResult = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objectID OUT
		IF @hResult <> 0 goto destroy
		EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'
		IF @hResult <> 0 goto destroy
		EXEC 	@hResult = sp_OAMethod @objectID, 'send', null
		IF @hResult <> 0 goto destroy
		DECLARE @t TABLE(s nvarchar(max))
		INSERT @t
			EXEC sp_OAGetProperty @objectID, 'responseText'
		IF @hResult <> 0 goto destroy
		DECLARE @LAT nvarchar(20)
		DECLARE @LON nvarchar(20)
		DECLARE @n int
		DECLARE @STRLEN int
		SELECT @STRLEN=LEN(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5))+1 FROM @t
		SELECT @n=CHARINDEX(' ',SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5)) FROM @t
		SELECT 
			@LON=SUBSTRING(s,CHARINDEX('<pos>',s)+5,@n),
			@LAT=SUBSTRING(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5),@n,@STRLEN-@n)
		FROM @t
		DECLARE @UpdateSQL AS VARCHAR(MAX)		
		SET @UpdateSQL = ' UPDATE [UT_TEST_COPY]. [dbo].[_Reference107] ' +
						 ' SET _Fld11002 = ' + RTRIM(LTRIM(@LAT)) + ',' +
						 ' _Fld11003 = ' + RTRIM(LTRIM(@LON)) + 
						 ' WHERE _CODE = '+CHAR(39)+ @ID1 + CHAR(39)
		EXECUTE(@UpdateSQL)
		destroy:
			exec sp_OADestroy @objectID
		DELETE FROM @t
		FETCH NEXT FROM curMarks
		INTO @URI1,@ID1;        
	END
CLOSE curMarks
DEALLOCATE curMarks

Шаг третий:


— Очистка таблицы импорта заявок на выезд в программе построения маршрутов «ANTOR LogisticsMaster»:

DELETE FROM [LMaster].[dbo].[D__IMPORT0]

Шаг четвертый:


— Выгрузка заявок на выезд в программу построения маршрутов «ANTOR LogisticsMaster»:

INSERT INTO [LMaster].[dbo].[D__IMPORT0]
       ([EXT_ID]
       ,[EXT_STRID]
       ,[OPER_ID]
       ,[ORD_TYP]
       ,[DELIV_DATE]
       ,[ROUTE_ID]
       ,[ROUTE_NUM]
       ,[NUM_INROUTE]
       ,[CUST_ID]
       ,[CUST_STRID]
       ,[RENTED]
       ,[UNLOAD_TYP]
       ,[CATEGORY_ID]
       ,[TIME_BEG]
       ,[TIME_END]
       ,[TIME_UNLOAD]
       ,[ACTIVE]
       ,[ZONE_ID]
       ,[ACCESS_ID]
       ,[OGRSUM1]
       ,[OGRSUM2]
       ,[SUM3]
       ,[SUM4]
       ,[ADDR]
       ,[DISTR]
       ,[TOWN]
       ,[STREET]
       ,[HOUS]
       ,[CORP]
       ,[LINKED]
       ,[X]
       ,[Y]
       ,[DISTANC]
       ,[TIME_ARR]
       ,[VIRT]
       ,[SOST]
       ,[STR1]
       ,[STR2]
       ,[STR3]
       ,[STR4]
       ,[STR5]
       ,[STR6]
       ,[INT1]
       ,[INT2]
       ,[INT3]
       ,[MIN_CAR]
       ,[MAX_CAR]
       ,[EXP1]
       ,[EXP2]
       ,[EXP3]
       ,[MACROZONE_ID]
       ,[PICT]
       ,[WIDTH]
       ,[HEIGHT]
       ,[LENGTH]
       ,[STORE1]
       ,[STORE2]
       ,[STORE3]
       ,[STORE4]
       ,[STORE5]
       ,[LATITUDE]
       ,[LONGITUDE])
 VALUES
       (NULL /*[EXT_ID], int*/
       ,'"+НомерДокумента_SQL+"' /*[EXT_STRID], nvarchar(36)*/
       ,0 /*[OPER_ID], int*/
       ,0 /*[ORD_TYP], int*/
       ,(Convert(datetime,'"+ДатаДокумента_SQL+"',104)) /*[DELIV_DATE], datetime*/
       ,NULL /*[ROUTE_ID], int*/
       ,NULL /*[ROUTE_NUM], int*/
       ,NULL /*[NUM_INROUTE], int*/
       ,NULL /*[CUST_ID], int*/
       ,'"+Код_SQL+"' /*[CUST_STRID], nvarchar(36)*/
       ,NULL /*[RENTED], bit*/
       ,0 /*[UNLOAD_TYP], int*/
       ,0 /*[CATEGORY_ID], int*/
       ,'"+ВремяДоставкиС_SQL+"' /*[TIME_BEG], datetime*/
       ,'"+ВремяДоставкиПо_SQL+"' /*[TIME_END], datetime*/
       ,(Convert(datetime,'19000101',104)) /*[TIME_UNLOAD], datetime*/
       ,NULL /*[ACTIVE], int*/
       ,"+Зона_SQL+" /*[ZONE_ID], int*/
       ,NULL /*[ACCESS_ID], int*/
       ,"+Забрать_SQL+" /*[OGRSUM1], float*/
       ,"+Доставить_SQL+" /*[OGRSUM2], float*/
       ,0 /*[SUM3], float*/
       ,0 /*[SUM4], float*/
       ,'"+Адрес_SQL+"' /*[ADDR], nvarchar(100)*/
       ,"+Регион_SQL+" /*[DISTR], nvarchar(50)*/
       ,"+Город_SQL+" /*[TOWN], nvarchar(50)*/
       ,"+Улица_SQL+" /*[STREET], nvarchar(50)*/
       ,"+Дом_SQL+" /*[HOUS], nvarchar(20)*/
       ,"+Корпус_SQL+" /*[CORP], nvarchar(20)*/
       ,NULL /*[LINKED], int*/
       ,NULL /*[X], int*/
       ,NULL /*[Y], int*/
       ,NULL /*[DISTANC], float*/
       ,NULL /*[TIME_ARR], datetime*/
       ,NULL /*[VIRT], int*/
       ,NULL /*[SOST], int*/
       ,'"+Наименование_SQL+"' /*[STR1], nvarchar(255)*/
       ,'"+Договор_SQL+"' /*[STR2], nvarchar(255)*/
       ,'"+Менеджер_SQL+"' /*[STR3], nvarchar(255)*/
       ,'"+Телефон_SQL+"' /*[STR4], nvarchar(255)*/
       ,'' /*[STR5], nvarchar(100)*/
       ,'"+Задание_SQL+"' /*[STR6], nvarchar(255)*/
       ,0 /*[INT1], int*/
       ,0 /*[INT2], int*/
       ,0 /*[INT3], int*/
       ,NULL /*[MIN_CAR], float*/
       ,NULL /*[MAX_CAR], float*/
       ,NULL /*[EXP1], nvarchar(36)*/
       ,NULL /*[EXP2], nvarchar(36)*/
       ,NULL /*[EXP3], nvarchar(36)*/
       ,NULL /*[MACROZONE_ID], int*/
       ,NULL /*[PICT], int*/
       ,NULL /*[WIDTH], float*/
       ,NULL /*[HEIGHT], float*/
       ,NULL /*[LENGTH], float*/
       ,NULL /*[STORE1], int*/
       ,NULL /*[STORE2], int*/
       ,NULL /*[STORE3], int*/
       ,NULL /*[STORE4], int*/
       ,NULL /*[STORE5], int*/
       ,"+Широта_SQL+" /*[LATITUDE], FLOAT*/
       ,"+Долгота_SQL+" /*[LONGITUDE], FLOAT*/)

Шаг пятый:


— Обновление статуса заявок на выезд в программе «1С: Управление торговлей, Версия 11»:

SELECT 
     D. EXT_STRID AS Код_CRM, 
     D.NAME AS Водитель, 
     O.TIME_ARR AS Прибытие, 
     O.EXT_STRID AS Номер, 
     O.DISTANC AS Расстояние, 
     R.LEN-(SELECT 
             sum(O1.DISTANC)
           FROM dbo.D__ORDERS0 AS O1
           WHERE O1.ROUTE_ID=R.ID) AS РасстояниеДоОфиса,
     O.LATITUDE AS LATITUDE, 
     O.LONGITUDE AS LONGITUDE, 
     O.ZONE_ID AS Код_ЗоныДоставки 
 FROM dbo.D__ZONE0 AS Z WITH(NOLOCK)
   INNER JOIN dbo.D__DRIVERS AS D WITH(NOLOCK)
   INNER JOIN dbo.D__CARS AS C WITH(NOLOCK) ON D.ID = C.DRIVER_ID 
   INNER JOIN dbo.D__ROUTE0 AS R WITH(NOLOCK) ON C.ID = R.CAR_ID ON Z.ID = R.ZONE_ID 
   RIGHT OUTER JOIN dbo.D__ORDERS0 AS O WITH(NOLOCK) ON R.ID = O.ROUTE_ID 
 WHERE 
     (O.SOST >= 0) AND (O.ROUTE_ID <> 0) 
 ORDER BY 
  D.NAME, 
  O.ROUTE_NUM, 
  O.NUM_INROUTE

Вы спросите как я до «такого» докатился?


Ответ приходит (если немного покопаться в памяти с психологом):

— 20 лет назад я «работал» на УАЗе в дружном коллективе отдела АСУП техником.

Работой я это назвать никак не могу (зеленый подросток вечно делающий все не так).

Всех ребят я помню и вспоминаю со слезами, как я по ним скучаю. К сожалению никак не получается найти координаты Давиденко Ивана Ивановича (именно он и отговаривал

меня от изучения языка T-SQL — запретный плод), слышал он ушел на Авиастар.

Поиск информации в тексте средствами MS SQL Server

Время прочтения: 4 мин.

В своей работе нам часто приходится анализировать текст, изложенный в свободной форме (назначения платежей, комментарии, адреса и др.) с целью вычленить определенную информацию. В excel для этого можно использовать текстовые формулы и регулярные выражения в VBA. Кроме того, регулярные выражения и текстовые функции можно использовать в python и других языках программирования.

Но как быть, если объем информации измеряется миллионами записей в базе данных, а времени на выгрузку и анализ небольших порций данных нет? Использование Transact-SQL (который хоть и не поддерживает регулярные выражения в полной мере, имеет более расширенный функционал оператора like, чем стандартный SQL) позволит переложить вычисления на плечи СУБД и сэкономить время на копировании данных. 0-9]%’
and [Номер телефона] not like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’
)
if @t like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%’
begin
update [dbo].[banki_ru_messages]
set [Номер телефона]=right([Номер телефона], len([Номер телефона])-12)
where [Номер телефона] =@t
end
else
begin
update [dbo].[banki_ru_messages]
set [Номер телефона]=right([Номер телефона], len([Номер телефона])-1)
where [Номер телефона] =@t
end
end
go
update [dbo].[banki_ru_messages]
set [Номер телефона]=left([Номер телефона], 11)
where [Номер телефона] is not null
go
alter table [dbo].[banki_ru_messages]
alter column [Номер телефона] nvarchar(11)
go
select * from [dbo].[banki_ru_messages]
where [Номер телефона] is not null

Аналогичным способом, изменив количество цифр, можно искать номера счетов, ИНН, почтовые индексы.

Для поиска адресов электронной почты можно воспользоваться следующим кодом:

alter table [dbo].[banki_ru_messages]
add [E-mail] nvarchar(max)
go
update [dbo]. [banki_ru_messages]
set [E-mail]=[Сообщение]
where [Сообщение] like '%[A-z0-9]@[A-z0-9]%'
go
declare @t nvarchar(max)
while exists (select top 1 [E-mail] from [dbo].[banki_ru_messages]
			  where [E-mail] is not null and charindex(' ', [E-mail])>0)
begin
	set @t=(select top 1 [E-mail] from [dbo].[banki_ru_messages]
			where [E-mail] is not null	and charindex(' ', [E-mail])>0)
	if left(@t, charindex(' ', @t)-1) like '%[A-z0-9]@[A-z0-9]%'
	begin
		update [dbo].[banki_ru_messages]
		set [E-mail]=left(@t, charindex(' ', @t)-1)
		where [E-mail]=@t
	end
	else
	begin
		update [dbo].[banki_ru_messages]
		set [E-mail]=right(@t, len(@t)-charindex(' ', @t)+1)
		where [E-mail]=@t
	end
end
go
select * from [dbo].[banki_ru_messages]
where [E-mail] is not null	or [ИНН] is not null or [Номер телефона] is not null

Описанные алгоритмы конечно же имеют погрешность (количество подряд идущих цифр не всегда однозначно определяет их суть, да и символ “@” встречается не только в адресах электронной почты), но в определенных ситуациях позволяют сэкономить время и дать представление о наличии и объеме искомой информации в текстовых полях.

SQL Server Функция CHARINDEX()

❮ Предыдущая
❮ Функции SQL-сервера
Далее ❯

Пример

Поиск «t» в строке «Клиент» и возврат позиции:

ВЫБОР
CHARINDEX(‘t’, ‘Клиент’) AS MatchPosition;

Попробуйте сами »

Определение и использование

Функция CHARINDEX() ищет подстроку в строке и возвращает значение
Положение.

Если подстрока не найдена, эта функция возвращает 0.

Примечание: Эта функция выполняет поиск без учета регистра.

Синтаксис

CHARINDEX( подстрока ,
строка , начало )

Значения параметров

.

Параметр Описание
подстрока Обязательно. Подстрока для поиска
строка Обязательно. Строка для поиска
начало Дополнительно. Позиция, с которой начнется поиск (если вы не хотите
начинаться с начала s tring ).
Первая позиция в строке равна 1

Технические детали

Работает в: SQL Server (начиная с 2008 г.), база данных SQL Azure, данные SQL Azure
Склад, параллельное хранилище данных

Другие примеры

Пример

Поиск «OM» в строке «Клиент» и возврат позиции:

SELECT
CHARINDEX(‘OM’, ‘Клиент’) AS MatchPosition;

Попробуйте сами »

Пример

Поиск «mer» в строке «Customer» и возврат позиции (начало в позиции
3):

SELECT CHARINDEX(‘mer’, ‘Customer’, 3) AS MatchPosition;

Попробуйте сами »

❮ Предыдущая
❮ Функции SQL-сервера
Следующий ❯

ВЫБОР ЦВЕТА

Лучшие учебники

Учебник по HTML
Учебник по CSS
Учебник по JavaScript
Учебник How To
Учебник по SQL
Учебник по Python
Учебник по W3. CSS
Учебник по Bootstrap
Учебник по PHP
Учебник по Java
Учебник по C++
Учебник по jQuery

900 Справочник

900
Справочник по HTML
Справочник по CSS
Справочник по JavaScript
Справочник по SQL
Справочник по Python
Справочник по W3.CSS
Справочник по Bootstrap
Справочник по PHP
Цвета HTML
Справочник по Java
Справочник по Angular
Справочник по jQuery

Основные примеры

Примеры HTML
Примеры CSS
Примеры JavaScript
Примеры инструкций
Примеры SQL
Примеры Python
Примеры W3.CSS
Примеры Bootstrap
Примеры PHP
Примеры Java
Примеры XML
Примеры jQuery

2

2 |
О

W3Schools оптимизирован для обучения и обучения. Примеры могут быть упрощены для улучшения чтения и обучения.
Учебники, ссылки и примеры постоянно пересматриваются, чтобы избежать ошибок, но мы не можем гарантировать полную правильность всего содержания.
Используя W3Schools, вы соглашаетесь прочитать и принять наши условия использования,
куки-файлы и политика конфиденциальности.

Copyright 1999-2023 Refsnes Data. Все права защищены.
W3Schools работает на основе W3.CSS.

Функция SQL CHARINDEX Использование и примеры

Автор: Greg Robidoux

Функция CHARINDEX используется для поиска начальной точки, где одна строка
существует внутри другой строки. Это часто используется с другими функциями, такими как
SUBSTRING, чтобы найти
начальная точка в строке.

Синтаксис 

 CHARINDEX(stringToFind, stringToSearch [startingPosition])
Параметры
  • stringToFind — это строка, которую вы хотите найти в
    строка для поиска.
  • stringToSearch — это строка, содержащая
    строка, которую вы ищете.
  • startPosition — startPosition является необязательным параметром и представляет собой число. Если это
    не указано, stringToSearch запускается в начале. Если значение
    указано, поиск начинается с указанного количества символов в строке stringToSearch.

Простой пример CHARINDEX

Ниже приведен простой пример использования CHARINDEX. Мы будем искать слово
«тест» в более длинной строке «Это тест».

 SELECT CHARINDEX('тест', 'Это тест')

Возвращает значение 11, так как значение «тест» начинается с позиции 11 в
строка для поиска.

 SELECT CHARINDEX('Это', 'Это тест')

Это возвращает значение 1, так как значение «Это» начинается с позиции 1 в
строка для поиска.

Пример CHARINDEX не начинается с первой позиции строки

Вот пример использования начальной позиции. Поскольку мы начинаем с
позиции 15, он пропустит первый «тест» и будет искать любые вхождения
«испытание» после этой позиции.

 SELECT CHARINDEX('тест', 'Это тест, это тест.', 15)

Возвращает значение 27, так как мы начинаем с позиции 15 и
поэтому он пропускает первый «тест» и находит второй «тест» на позиции 27 в
строка для поиска.

Найти все вхождения строки в строке Пример

Следующий код является запросом к базе данных AdventureWorks. Он запрашивает
таблицу Production.ProductDescription, чтобы найти все вхождения строки в
столбец Описание.

Ниже мы ищем слово «велосипед» и возвращаем строки, в которых
позиция больше 0,

 DECLARE @searchValue nvarchar(20) = 'велосипед';
С CTE(ProductDescriptionID, позиция)
КАК (
   ВЫБЕРИТЕ ProductDescriptionID, CHARINDEX(@searchValue, Описание)
   ОТ [Производство].[Описание продукта]
   СОЮЗ ВСЕХ
   ВЫБЕРИТЕ a.ProductDescriptionID, CHARINDEX(@searchValue, a.Description, CTE.position + 1)
   ОТ [Производство].[Описание продукта] a
     ВНУТРЕННЕЕ СОЕДИНЕНИЕ CTE НА A.ProductDescriptionID = cte.ProductDescriptionID
   ГДЕ CHARINDEX(@searchValue, a.Description, CTE.position + 1) > 0
)
ВЫБИРАТЬ *
ИЗ КТЭ
ГДЕ позиция > 0
ПОРЯДОК НА 1,2;

Выделенные элементы ниже — это записи, в которых имеется более 1
появления искомого слова.