Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 URL decode  [new]
Edgar
Member

Откуда: Лондон
Сообщений: 434
Как декодировать строчку содержащую кодированные символы?

например строчка

500%20pc.%20English%2C

должна быть декодирована в

500 pc. English,

Мне нужно проапдейтить такие вот "кодированные строчки" в базе.

update table1 set text_value = DecodeURL(text_value)

Может кто то встречал такую вот функцию?
1 июл 11, 12:24    [10905319]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Один из старых вариантов. Так все давно на CLR переписали.
if not exists (select * from dbo.sysobjects where id = object_id('dbo.df_decode_url') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
	exec('create function dbo.df_decode_url () returns int as begin return 0 end')
GO
alter function dbo.df_decode_url(
	@url			nvarchar(1001)
) returns nvarchar(260)
with schemabinding
as begin

	declare
		@pos		int
		, @ui		uniqueidentifier = '00000000-0000-0000-0000-000000000000'
		, @hbyte	tinyint	= 0
		, @lbyte	tinyint	= 0
		, @bcount	tinyint = 0
		, @word		int		= 0
		, @chars	nvarchar(2) = ''
		, @strbuff	nvarchar(1001) = @url
		

	if @url is null or @url = '' /*or @url like '%?%'*/ begin
		set @strbuff = null
		goto the_end
	end
	
	while (1 = 1) begin	
		set @pos = patindex('%[%][a-f0-9][a-f0-9]%', @strbuff) 	

		if @pos = 0 begin
			break
		end


		-- Вычленяем старший hex-байт в UTF-8 
		-- Скуль не умеет сразу отконвертить в int, 
		-- поэтому ударим в бубен через uniqident
		
		set @ui = 
			stuff(@ui, 7, 2,
				left(
					right(@strbuff,len(@strbuff) - @pos)
					,2
				)
			)

		set @hbyte =
			cast(
				cast( 
					@ui
					as binary(1)	
				) 
				as int
			)
			
		--print @hbyte	
		-- Проверяем, к какому диапазону относится байт
		
		if @hbyte between 0 and 127 begin
			--(US-ASCII) прямая конвертация UTF-8 -> UTF-16
			select 
				@word = @hbyte 
				, @bcount = 1
		end else if @hbyte between 128 and 191 begin
				-- illegal
				set @strbuff = @url
				break	
		end	else if @hbyte between 192 and 223 begin
			select 
				@word = @hbyte  & 31 
				, @bcount = 2	
				
		end	else if @hbyte between 224 and 239 begin
			select 
				@word = @hbyte & 15
				, @bcount = 3		
		end else if @hbyte between 240 and 244 begin
			select 
				@word = @hbyte & 7
				, @bcount = 4		
		end else begin
				-- не совсем иллегал, но это имеет отношение к расширенному стандарту
				set @strbuff = @url
				break			
		end

		declare
			@i int = 1
		
		while (@i < @bcount) begin


			-- Проверяем, что оставшаяся длина символов соответствует bcount
			if (len(@strbuff) - @pos - 3* @i /*(@bcount -1)*/) < 0  begin
				set @strbuff = @url
				goto the_end
			end 

			set @chars = 
				left(
					right(@strbuff,len(@strbuff) - @pos - 3* @i /*(@bcount -1)*/)
					,2
				)			
				
			-- Проверяем, что это hex
			
			if @chars not like '[a-f0-9][a-f0-9]' begin
				set @strbuff = @url
				goto the_end
			end 			
			
			-- не умеет сразу отконвертить в int, поэтому ударим в бубен	
			set @ui = stuff(@ui, 7, 2, @chars)		

			set @lbyte = 
				cast(
					cast( 
						@ui
						as binary(1)	
					) 
					as int
				)	
	
			-- Проверяем на illegal
			if @lbyte not between 128 and 191 begin
				set @strbuff = @url
				goto the_end
			end 
			
			select
				@word = (@word * 64 /*power(2, 6)*/) | (@lbyte & 63)				


			set @i += 1
		end
		
		
		if @bcount > 1 
			set @strbuff = stuff(@strbuff, @pos, 3*(@bcount), nchar(@word))
		else begin
			set @strbuff = stuff(@strbuff, @pos, 2, nchar(@word))
			set @strbuff = stuff(@strbuff, @pos+1, 1, N'')		
		end


				
		--select @strbuff
	end	
the_end:
	return @strbuff

end
go

З.Ы. Писал не я. ;)
1 июл 11, 12:29    [10905361]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
Гавриленко Сергей Алексеевич
З.Ы. Писал не я. ;)
извинения за костыли в комментах улыбают :)
1 июл 11, 12:43    [10905478]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Гадя Петрович
Гавриленко Сергей Алексеевич
З.Ы. Писал не я. ;)
извинения за костыли в комментах улыбают :)
Не, серьезно. ;)

А костыли, как и каменты, у нас норм.
1 июл 11, 12:45    [10905490]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
aleks2
Guest
Гавриленко Сергей Алексеевич
З.Ы. Писал не я. ;)


За такое - руки отрывать нада.

CREATE TABLE [dbo].[CharSubst](
	[symbol] [char](3) NOT NULL,
	[char] [nchar](1) NOT NULL,
 CONSTRAINT [PK_CharSubst] PRIMARY KEY CLUSTERED 
(
	[symbol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go

insert dbo.CharSubst
select '%2C', ','
insert dbo.CharSubst
select '%20', ' '
--... всего то 256 символов
go

CREATE function [dbo].[DecodeURL](@URL varchar(4000))
returns nvarchar(4000)
as
begin
select @URL=REPLACE(@URL, symbol, [char] ) FROM dbo.CharSubst
return @URL
end
go

select dbo.DecodeUrl('500%20pc.%20English%2C')
1 июл 11, 19:52    [10909086]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
aleks2
--... всего то 256 символов
А если подумать чуть-чуть, и вспомнить, что юникод двухбайтовый? Попробовать http://ru.wikipedia.org/wiki/%D0%9C%D0%B8%D0%BA%D1%80%D0%BE%D0%BA%D1%80%D0%B5%D0%B4%D0%B8%D1%82 декодировать?
1 июл 11, 21:39    [10909489]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Гавриленко Сергей Алексеевич
aleks2
--... всего то 256 символов
А если подумать чуть-чуть, и вспомнить, что юникод двухбайтовый? Попробовать http://ru.wikipedia.org/wiki/%D0%9C%D0%B8%D0%BA%D1%80%D0%BE%D0%BA%D1%80%D0%B5%D0%B4%D0%B8%D1%82 декодировать?


Уууууу.... Тогда НАМНОГО сложнее... )))

CREATE TABLE [dbo].[CharSubst](
	[symbol] [char](3) NOT NULL,
	[char] [nchar](1) NOT NULL,
 CONSTRAINT [PK_CharSubst] PRIMARY KEY CLUSTERED 
(
	[symbol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go

[src]CREATE TABLE [dbo].[CharSubst2](
	[symbol] [char](6) NOT NULL,
	[char] [nchar](1) NOT NULL,
 CONSTRAINT [PK_CharSubst2] PRIMARY KEY CLUSTERED 
(
	[symbol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go

insert dbo.CharSubst
select '%2C', ','
insert dbo.CharSubst
select '%20', ' '
--... всего то 256 символов
go

insert dbo.CharSubst2 select '%D0%9C', 'М'
union all select '%D0%B8', 'и'
union all select '%D0%BA', 'к'
--... всего то 65536 символов )))
-- сгенерить можно процедурой
go

CREATE function [dbo].[DecodeURL](@URL varchar(4000))
returns nvarchar(4000)
as
begin
select @URL=REPLACE(@URL, symbol, [char] ) FROM dbo.CharSubst2
select @URL=REPLACE(@URL, symbol, [char] ) FROM dbo.CharSubst
return @URL
end
go

select dbo.DecodeUrl('500%20pc.%20English%2C')
1 июл 11, 23:32    [10909978]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
aleks2
--... всего то 65536 символов )))
А теперь внимание, вопрос: зачем на каждый урл гонять 65к+ реплейсов?
Если сделаешь полностью рабочий код, могу забабахать тест лямах на 50 урлов.
1 июл 11, 23:42    [10910027]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
З.Ы. Код читабельнее, согласен.
1 июл 11, 23:44    [10910034]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Гавриленко Сергей Алексеевич,

ну, попробую домой сперва дойти...
Я все еще на работе.
65к - НЕ будет в той табличке.
Даже 10% от 65к не будет.

Так что не факт, кто и победит.
1 июл 11, 23:45    [10910037]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Гавриленко Сергей Алексеевич,

мы как бы русскоязычные.
скорее всего нас устроит набор 255 (латиница+кириллица)
плюс пяток клонов кириллицы, плюс пара скандинавов, плюс.... кажись и всё.

Всё иероглифы и арабская вязь - фтопку.

так что максимум, что нас ждет в двухбайтной табличке - это 600-900 записей.
1 июл 11, 23:51    [10910062]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Makar4ik
Гавриленко Сергей Алексеевич,

мы как бы русскоязычные.
скорее всего нас устроит набор 255 (латиница+кириллица)
плюс пяток клонов кириллицы, плюс пара скандинавов, плюс.... кажись и всё.

Всё иероглифы и арабская вязь - фтопку.

так что максимум, что нас ждет в двухбайтной табличке - это 600-900 записей.
Это вы. Вам хорошо. А у меня урлы со всего мира, и мне их все надо декодировать. Да, и китайские иероглифы тоже. И вязь. И еще стопудово найдется хрень, о которой никто из нас понятия не имеет.

Сообщение было отредактировано: 1 июл 11, 23:54
1 июл 11, 23:52    [10910070]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Гавриленко Сергей Алексеевич,

гут.
Скажите мне пжалста, как вы их декодируете в CP1251??
или всё-таки, действительно в базе весь текст nchar???

Судя по ссылке на микрокредиты мы счса занимаемся чем-то похожим по работе...
У меня в базе нету ни одного текстового юникодного поля...
Ну как-то не пишут в России серию паспорта и адрес иероглифами...
1 июл 11, 23:58    [10910088]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Makar4ik
Гавриленко Сергей Алексеевич,
или всё-таки, действительно в базе весь текст nchar???
Не весь, а тот, который надо. Урлы - в nvarchar.
2 июл 11, 00:01    [10910100]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
Гавриленко Сергей Алексеевич,

доехал домой...
честно?
Ну не хочется мне писать работоспособный код для тестинга того, что мне не нужно :)
Я даже согласен спор проиграть, или не дискутировать вовсе.
Просто вот я так прикинул, что для русских урл-ов будет не более 1000 записей в обоих табличках.
2 июл 11, 01:28    [10910381]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Anatoly Podgoretsky
Member

Откуда:
Сообщений: 62908
Гавриленко Сергей Алексеевич
aleks2
--... всего то 256 символов
А если подумать чуть-чуть, и вспомнить, что юникод двухбайтовый? Попробовать http://ru.wikipedia.org/wiki/%D0%9C%D0%B8%D0%BA%D1%80%D0%BE%D0%BA%D1%80%D0%B5%D0%B4%D0%B8%D1%82 декодировать?

Тут вообще то UTF-8 закодирован. Для превращения в Юникод или ANSI нужны еще дополнительные шаги.
2 июл 11, 09:05    [10910713]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
aleks2
Guest
Anatoly Podgoretsky
Гавриленко Сергей Алексеевич
пропущено...
А если подумать чуть-чуть, и вспомнить, что юникод двухбайтовый? Попробовать http://ru.wikipedia.org/wiki/%D0%9C%D0%B8%D0%BA%D1%80%D0%BE%D0%BA%D1%80%D0%B5%D0%B4%D0%B8%D1%82 декодировать?

Тут вообще то UTF-8 закодирован. Для превращения в Юникод или ANSI нужны еще дополнительные шаги.


Ой, блин, знаток! Ты будешь утверждать, что %D0%9C означает РАЗНЫЙ символ в РАЗНЫХ местах?

А если нет - тады все однофигственно.
2 июл 11, 09:42    [10910763]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Anatoly Podgoretsky
Member

Откуда:
Сообщений: 62908
Не путай с Base64
2 июл 11, 09:53    [10910785]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
aleks2,

всё проще.
сперва разбираем юникод, потом однобайтные последовательности.
Всё в приведенном коде.
2 июл 11, 09:56    [10910787]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
aleks2
Guest
Anatoly Podgoretsky
Не путай с Base64

Это ты не путай божий дар с яичницей. Какая, нафег, разница Base64 иль Unicode или ишо какая херня. Декодирующая таблица - универсальное решение.


Makar4ik
aleks2,

всё проще.
сперва разбираем юникод, потом однобайтные последовательности.
Всё в приведенном коде.

Спасиба, а то я бы никогда не догадался. Хе-хе...
2 июл 11, 17:33    [10911687]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2680
aleks2
Декодирующая таблица - универсальное решение.
Никогда нет универсальной гарантии того, что универсальное решение не будет работать универсально плохо.
3 июл 11, 00:00    [10912552]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
aleks2
Guest
Makar4ik
aleks2
Декодирующая таблица - универсальное решение.
Никогда нет универсальной гарантии того, что универсальное решение не будет работать универсально плохо.


1. Зато БУДЕТ работать.
2. Всегда есть возможность усовершенствовать...
3. Даже если разбирать строку на символы, все одно замену проще и быстрее вести по таблице.
3 июл 11, 06:56    [10913237]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
aleks2
Guest
ALTER function [dbo].[DecodeURLex](@URL nvarchar(4000))
returns nvarchar(4000)
as
begin
;with
us as (
select cast(N'' as nvarchar(4000)) as b, @URL+N'%%%' as e
UNION ALL
select b+CASE S WHEN N'%%%' then LEFT(e,N-1) ELSE SUBSTRING(e,1,N-1)+(select char from dbo.CharSubst WHERE symbol=S) END b
, CASE S WHEN N'%%%' then N'' ELSE SUBSTRING(e,N+3,LEN(e)) END
from (select *, SUBSTRING(e,N,3) S FROM (select *, charindex('%', e) N from us) X WHERE N>0) us
)
select @URL=b FROM us WHERE e=N''
return @URL
end
3 июл 11, 07:22    [10913247]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Гавриленко Сергей Алексеевич
end else if @hbyte between 240 and 244
	select	 @word = @hbyte & 7
		,@bcount = 4		
Тут ошибка. Сиквел поддерживает только двух-байтовый код. А этот уже за него выпирает.

aleks2, у UTF-8 во втором и третьем и т.д. байтах могут встречаться одинаковые. Размер у него плавающий.

Если всё упростить (почти), то получится это:
CREATE FUNCTION [dbo].[fnDecodeURL] (
	 @URL	NVarChar(4000)
) RETURNS NVarChar(4000) WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING AS BEGIN	-- SELECT dbo.fnDecodeURL('http://ru.wikipedia.org/wiki/%D0%9C%D0%B8%D0%BA%D1%80%D0%BE%D0%BA%D1%80%D0%B5%D0%B4%D0%B8%D1%82')
	WITH Decode (URL,Pos) AS (
		SELECT	 @URL
			,NullIf(PatIndex(N'%[%][0-7C-E][0-9A-F]%',@URL),0)
	UNION ALL
		SELECT	 U.URL
			,NullIf(PatIndex(N'%[%][0-7C-E][0-9A-F]%',Stuff(U.URL,1,D.Pos,'')),0) + D.Pos
		FROM	Decode							D
			CROSS APPLY (	SELECT SubString(D.URL,D.Pos + 1,8))	X (Part)
			CROSS APPLY (	SELECT 3, Convert(Binary(3),          Convert(Binary(3),Convert(UniqueIdentifier,Stuff('00000000-0000-0000-0000-000000000000',7,2,Replace(Left(X.Part,2),'%','')))))	WHERE X.Part LIKE '[0-7][0-9A-F]%'
			UNION ALL	SELECT 6, Convert(Binary(3),4136704 & Convert(Binary(3),Convert(UniqueIdentifier,Stuff('00000000-0000-0000-0000-000000000000',5,4,Replace(Left(X.Part,5),'%','')))))	WHERE X.Part LIKE '[CD][0-9A-F][%][89AB][0-9A-F]%'
			UNION ALL	SELECT 9, Convert(Binary(3),4144927 & Convert(Binary(3),Convert(UniqueIdentifier,Stuff('00000000-0000-0000-0000-000000000000',3,6,Replace(Left(X.Part,8),'%','')))))	WHERE X.Part LIKE 'E[0-9A-F][%][89AB][0-9A-F][%][89AB][0-9A-F]%'
									)	C ([Size],UTF8)
			CROSS APPLY (	SELECT Stuff(D.URL,D.Pos,C.[Size],NChar(
				  SubString(C.UTF8,1,1)
				+ SubString(C.UTF8,2,1) * 64
				+ SubString(C.UTF8,3,1) * 4096))	)	U(URL)
		WHERE	D.Pos > 0
	)	SELECT	@URL = URL
		FROM	Decode
		WHERE	Pos IS NULL
	RETURN	@URL
END
GO
Т.к. писал на 2005м, то пришлось воспользоваться UniqueIdentifier-ным преобразованием HEX-а в Binary. Для 2008-го заменить:
Convert(Binary(3),Convert(UniqueIdentifier,Stuff('00000000-0000-0000-0000-000000000000',X,Y,...)))
Convert(Binary(3),...,2)
14 дек 11, 04:37    [11761375]     Ответить | Цитировать Сообщить модератору
 Re: URL decode  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Для 2008 чуть по другому. Replace хуже Stuff-ов:
CREATE FUNCTION [dbo].[fnURLDecode] (
	 @URL	NVarChar(4000)
) RETURNS NVarChar(4000) WITH RETURNS NULL ON NULL INPUT, SCHEMABINDING AS BEGIN	-- SELECT dbo.fnURLDecode('http://ru.wikipedia.org/wiki/%D0%9C%D0%B8%D0%BA%D1%80%D0%BE%D0%BA%D1%80%D0%B5%D0%B4%D0%B8%D1%82')
	WITH Decode (URL,Pos) AS (
		SELECT	 @URL
			,NullIf(PatIndex(N'%[%][0-7C-E][0-9A-F]%',@URL),0)
	UNION ALL
		SELECT	 U.URL
			,NullIf(PatIndex(N'%[%][0-7C-E][0-9A-F]%',Stuff(U.URL,1,D.Pos,'')),0) + D.Pos
		FROM	Decode							D
			CROSS APPLY (	SELECT SubString(D.URL,D.Pos + 1,8))	X (Part)
			CROSS APPLY (	SELECT 3,                             Convert(Binary(3),'0000' +     Left(X.Part,2)                ,2)	WHERE X.Part LIKE '[0-7][0-9A-F]%'
			UNION ALL	SELECT 6, Convert(Binary(3),   7999 & Convert(Binary(3),'00' + Stuff(Left(X.Part,5)        ,3,1,''),2))	WHERE X.Part LIKE '[CD][0-9A-F][%][89AB][0-9A-F]%'
			UNION ALL	SELECT 9, Convert(Binary(3),2047807 & Convert(Binary(3), Stuff(Stuff(Left(X.Part,8),6,1,''),3,1,''),2))	WHERE X.Part LIKE 'E[0-9A-F][%][89AB][0-9A-F][%][89AB][0-9A-F]%'
									)	C ([Size],UTF8)
			CROSS APPLY (	SELECT Stuff(D.URL,D.Pos,C.[Size],NChar(
				  SubString(C.UTF8,3,1)
				+ SubString(C.UTF8,2,1) * 64
				+ SubString(C.UTF8,1,1) * 4096))	)	U (URL)
		WHERE	D.Pos > 0
	)	SELECT	@URL = URL
		FROM	Decode
		WHERE	Pos IS NULL
	RETURN	@URL
END
GO
14 дек 11, 12:07    [11762828]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить