Мой Kbyte.Ru
Рассылка Kbyte.Ru
Группы на Kbyte.Ru
Партнеры Kbyte.Ru
Реклама
Сделано руками
Сделано руками
> Исходные коды - Lok_II -

Microsoft SQL Server - Строки

Все примеры / Строки

Преобразование текста в транслит

Автор: Lok_II | добавлено: 11.01.2011, 11:59 | просмотров: 7202 (1+) | комментариев: 2 | рейтинг: *x2
Готовая TSQL-функция для преобразования русского текста в транслит в соответствии с ГОСТ 7.79-2000.

Код

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
Об авторе

Lok_II

Нет информации об авторе...
Lok_II
Последние комментарии (всего: 2)

Добавлять комментарии могут только зарегистрированные пользователи сайта.
Если у Вас уже есть учетная запись на Kbyte.Ru, пройдите процедуру авторизации OpenID.
Если Вы еще не зарегистрированы на Kbyte.Ru - зарегистрируйтесь.

Запрос: select dbo.Translit('Маша ела кашу')
Ответ: Mhhasa ela kasuh
Если вас не устраивают такие ответы (почему Mhh? откуда на конце h?),
то вот вам мой вариант кода, правильный и топорный:

--DROP FUNCTION [dbo].[Translit]

CREATE FUNCTION [dbo].[Translit] (@str varchar(4000))
RETURNS varchar(4000) AS
BEGIN
declare @str_lat varchar(8000)
declare @i int, @ch varchar(2)
set @i = 1
set @str_lat = ''
while @i <= len(@str)
begin
set @ch = substring(@str, @i, 1)

if @ch Collate Cyrillic_General_CS_AS ='А' set @ch='A'
if @ch Collate Cyrillic_General_CS_AS ='а' set @ch='a'

if @ch Collate Cyrillic_General_CS_AS='Б' set @ch='B'
if @ch Collate Cyrillic_General_CS_AS='б' set @ch='b'

if @ch Collate Cyrillic_General_CS_AS='Г' set @ch='G'
if @ch Collate Cyrillic_General_CS_AS='г' set @ch='g'

if @ch Collate Cyrillic_General_CS_AS='Д' set @ch='D'
if @ch Collate Cyrillic_General_CS_AS='д' set @ch='d'

if @ch Collate Cyrillic_General_CS_AS='Е' set @ch='E'
if @ch Collate Cyrillic_General_CS_AS='е' set @ch='e'

if @ch Collate Cyrillic_General_CS_AS='Ж' set @ch='Zh'
if @ch Collate Cyrillic_General_CS_AS='ж' set @ch='zh'

if @ch Collate Cyrillic_General_CS_AS='З' set @ch='Z'
if @ch Collate Cyrillic_General_CS_AS='з' set @ch='z'

if @ch Collate Cyrillic_General_CS_AS='И' set @ch='I'
if @ch Collate Cyrillic_General_CS_AS='и' set @ch='i'

if @ch Collate Cyrillic_General_CS_AS='Й' set @ch='Y'
if @ch Collate Cyrillic_General_CS_AS='й' set @ch='y'

if @ch Collate Cyrillic_General_CS_AS='К' set @ch='K'
if @ch Collate Cyrillic_General_CS_AS='к' set @ch='k'

if @ch Collate Cyrillic_General_CS_AS='Л' set @ch='L'
if @ch Collate Cyrillic_General_CS_AS='л' set @ch='l'

if @ch Collate Cyrillic_General_CS_AS='М' set @ch='M'
if @ch Collate Cyrillic_General_CS_AS='м' set @ch='m'

if @ch Collate Cyrillic_General_CS_AS='Н' set @ch='N'
if @ch Collate Cyrillic_General_CS_AS='н' set @ch='n'

if @ch Collate Cyrillic_General_CS_AS='О' set @ch='O'
if @ch Collate Cyrillic_General_CS_AS='о' set @ch='o'

if @ch Collate Cyrillic_General_CS_AS='П' set @ch='P'
if @ch Collate Cyrillic_General_CS_AS='п' set @ch='p'

if @ch Collate Cyrillic_General_CS_AS='Р' set @ch='R'
if @ch Collate Cyrillic_General_CS_AS='р' set @ch='r'

if @ch Collate Cyrillic_General_CS_AS='С' set @ch='S'
if @ch Collate Cyrillic_General_CS_AS='с' set @ch='s'

if @ch Collate Cyrillic_General_CS_AS='Т' set @ch='T'
if @ch Collate Cyrillic_General_CS_AS='т' set @ch='t'

if @ch Collate Cyrillic_General_CS_AS='У' set @ch='U'
if @ch Collate Cyrillic_General_CS_AS='у' set @ch='u'

if @ch Collate Cyrillic_General_CS_AS='Ф' set @ch='F'
if @ch Collate Cyrillic_General_CS_AS='ф' set @ch='f'

if @ch Collate Cyrillic_General_CS_AS='Х' set @ch='Kh'
if @ch Collate Cyrillic_General_CS_AS='х' set @ch='kh'

if @ch Collate Cyrillic_General_CS_AS='Ц' set @ch='Ts'
if @ch Collate Cyrillic_General_CS_AS='ц' set @ch='ts'

if @ch Collate Cyrillic_General_CS_AS='Ч' set @ch='Ch'
if @ch Collate Cyrillic_General_CS_AS='ч' set @ch='ch'

if @ch Collate Cyrillic_General_CS_AS='Ш' set @ch='Sh'
if @ch Collate Cyrillic_General_CS_AS='ш' set @ch='sh'

if @ch Collate Cyrillic_General_CS_AS='Щ' set @ch='Shch'
if @ch Collate Cyrillic_General_CS_AS='щ' set @ch='shch'

if (@ch Collate Cyrillic_General_CS_AS='ъ')or
(@ch Collate Cyrillic_General_CS_AS='Ъ')or
(@ch Collate Cyrillic_General_CS_AS='ь')or
(@ch Collate Cyrillic_General_CS_AS='Ь') set @ch='~'

if @ch Collate Cyrillic_General_CS_AS='Ы' set @ch='Y'
if @ch Collate Cyrillic_General_CS_AS='ы' set @ch='y'

if @ch Collate Cyrillic_General_CS_AS='Э' set @ch='E'
if @ch Collate Cyrillic_General_CS_AS='э' set @ch='e'

if @ch Collate Cyrillic_General_CS_AS='Ю' set @ch='Yu'
if @ch Collate Cyrillic_General_CS_AS='ю' set @ch='yu'

if @ch Collate Cyrillic_General_CS_AS='Я' set @ch='Ya'
if @ch Collate Cyrillic_General_CS_AS='я' set @ch='ya'

set @str_lat=@str_lat+@ch
set @i = @i + 1
end
return @str_lat
END

Запрос: select dbo.Translit('Маша ела кашу')
Ответ: Masha ela kashu


Забыл ВвЁё. Вот как правильно:

--DROP FUNCTION [dbo].[Translit]

CREATE FUNCTION [dbo].[Translit] (@str varchar(4000))
RETURNS varchar(4000) AS
BEGIN
declare @str_lat varchar(8000)
declare @i int, @ch varchar(2)
set @i = 1
set @str_lat = ''
while @i <= len(@str)
begin
set @ch = substring(@str, @i, 1)

if @ch Collate Cyrillic_General_CS_AS ='А' set @ch='A'
if @ch Collate Cyrillic_General_CS_AS ='а' set @ch='a'

if @ch Collate Cyrillic_General_CS_AS='Б' set @ch='B'
if @ch Collate Cyrillic_General_CS_AS='б' set @ch='b'

if @ch Collate Cyrillic_General_CS_AS='В' set @ch='V'
if @ch Collate Cyrillic_General_CS_AS='в' set @ch='v'

if @ch Collate Cyrillic_General_CS_AS='Г' set @ch='G'
if @ch Collate Cyrillic_General_CS_AS='г' set @ch='g'

if @ch Collate Cyrillic_General_CS_AS='Д' set @ch='D'
if @ch Collate Cyrillic_General_CS_AS='д' set @ch='d'

if (@ch Collate Cyrillic_General_CS_AS='Е')or
(@ch Collate Cyrillic_General_CS_AS='Ё') set @ch='E'
if (@ch Collate Cyrillic_General_CS_AS='е')or
(@ch Collate Cyrillic_General_CS_AS='ё') set @ch='e'

if @ch Collate Cyrillic_General_CS_AS='Ж' set @ch='Zh'
if @ch Collate Cyrillic_General_CS_AS='ж' set @ch='zh'

if @ch Collate Cyrillic_General_CS_AS='З' set @ch='Z'
if @ch Collate Cyrillic_General_CS_AS='з' set @ch='z'

if @ch Collate Cyrillic_General_CS_AS='И' set @ch='I'
if @ch Collate Cyrillic_General_CS_AS='и' set @ch='i'

if @ch Collate Cyrillic_General_CS_AS='Й' set @ch='Y'
if @ch Collate Cyrillic_General_CS_AS='й' set @ch='y'

if @ch Collate Cyrillic_General_CS_AS='К' set @ch='K'
if @ch Collate Cyrillic_General_CS_AS='к' set @ch='k'

if @ch Collate Cyrillic_General_CS_AS='Л' set @ch='L'
if @ch Collate Cyrillic_General_CS_AS='л' set @ch='l'

if @ch Collate Cyrillic_General_CS_AS='М' set @ch='M'
if @ch Collate Cyrillic_General_CS_AS='м' set @ch='m'

if @ch Collate Cyrillic_General_CS_AS='Н' set @ch='N'
if @ch Collate Cyrillic_General_CS_AS='н' set @ch='n'

if @ch Collate Cyrillic_General_CS_AS='О' set @ch='O'
if @ch Collate Cyrillic_General_CS_AS='о' set @ch='o'

if @ch Collate Cyrillic_General_CS_AS='П' set @ch='P'
if @ch Collate Cyrillic_General_CS_AS='п' set @ch='p'

if @ch Collate Cyrillic_General_CS_AS='Р' set @ch='R'
if @ch Collate Cyrillic_General_CS_AS='р' set @ch='r'

if @ch Collate Cyrillic_General_CS_AS='С' set @ch='S'
if @ch Collate Cyrillic_General_CS_AS='с' set @ch='s'

if @ch Collate Cyrillic_General_CS_AS='Т' set @ch='T'
if @ch Collate Cyrillic_General_CS_AS='т' set @ch='t'

if @ch Collate Cyrillic_General_CS_AS='У' set @ch='U'
if @ch Collate Cyrillic_General_CS_AS='у' set @ch='u'

if @ch Collate Cyrillic_General_CS_AS='Ф' set @ch='F'
if @ch Collate Cyrillic_General_CS_AS='ф' set @ch='f'

if @ch Collate Cyrillic_General_CS_AS='Х' set @ch='Kh'
if @ch Collate Cyrillic_General_CS_AS='х' set @ch='kh'

if @ch Collate Cyrillic_General_CS_AS='Ц' set @ch='Ts'
if @ch Collate Cyrillic_General_CS_AS='ц' set @ch='ts'

if @ch Collate Cyrillic_General_CS_AS='Ч' set @ch='Ch'
if @ch Collate Cyrillic_General_CS_AS='ч' set @ch='ch'

if @ch Collate Cyrillic_General_CS_AS='Ш' set @ch='Sh'
if @ch Collate Cyrillic_General_CS_AS='ш' set @ch='sh'

if @ch Collate Cyrillic_General_CS_AS='Щ' set @ch='Shch'
if @ch Collate Cyrillic_General_CS_AS='щ' set @ch='shch'

if (@ch Collate Cyrillic_General_CS_AS='ъ')or
(@ch Collate Cyrillic_General_CS_AS='Ъ')or
(@ch Collate Cyrillic_General_CS_AS='ь')or
(@ch Collate Cyrillic_General_CS_AS='Ь') set @ch='~'

if @ch Collate Cyrillic_General_CS_AS='Ы' set @ch='Y'
if @ch Collate Cyrillic_General_CS_AS='ы' set @ch='y'

if @ch Collate Cyrillic_General_CS_AS='Э' set @ch='E'
if @ch Collate Cyrillic_General_CS_AS='э' set @ch='e'

if @ch Collate Cyrillic_General_CS_AS='Ю' set @ch='Yu'
if @ch Collate Cyrillic_General_CS_AS='ю' set @ch='yu'

if @ch Collate Cyrillic_General_CS_AS='Я' set @ch='Ya'
if @ch Collate Cyrillic_General_CS_AS='я' set @ch='ya'

set @str_lat=@str_lat+@ch
set @i = @i + 1
end
return @str_lat
END
GO
--GRANT EXECUTE ON OBJECT::[dbo].[Translit] TO public;
--GO
Авторизация
 
OpenID
Зарегистрируйся и получи 10% скидку на добавление своего сайта в каталоги! Подробнее »
Поиск по сайту
Люди на Kbyte.Ru
Реклама
Счетчики