SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Translit] (@str varchar(4000))  
RETURNS varchar(4000)  AS  
BEGIN 
 	

	-- транслитерация по ГОСТ-а 7.79-2000
	
	declare @str_lat varchar(8000)
	declare @rus varchar(100), @lat1 varchar(100), @lat2 varchar(100), @lat3 varchar(100)
	set @rus =  'абвгдеёжзийклмнопрстуфхцчшщъыьэюя'
	set @lat1 = 'abvgdejzzijklmnoprstufkccss"y''ejj' 
	set @lat2 = '      oh  j           h hhh   hua'
	set @lat3 = '                          h      '
	
	declare @i int, @pos int, @ch varchar(2)
	set @i = 1
	set @str_lat = ''
	
	while @i <= len(@str)
	begin
		set @ch = substring(@str, @i, 1)
		set @pos = charindex(lower(@ch), @rus)
	
		if @pos > 0
		begin
			if ascii(upper(@ch)) = ascii(@ch)
				set @str_lat = @str_lat + upper(substring(@lat1, @pos, 1)) + rtrim(substring(@lat2, @pos, 1)) + rtrim(substring(@lat3, @pos, 1))
			else
				set @str_lat = @str_lat + substring(@lat1, @pos, 1) + rtrim(substring(@lat2, @pos, 1)) + rtrim(substring(@lat3, @pos, 1))
		end
		else
			set @str_lat = @str_lat + @ch
		set @i = @i + 1
	end
	
	return @str_lat
END