Транслитерация на MS SQL

Пара функций на MS SQL по транслитерации русского текста в английский.



Одна из вариаций

GO
/****** Object:  UserDefinedFunction [dbo].[TransLit]    Script Date: 05.04.2017 10:25:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[TransLit]
(
 @@String VarChar(max)
)
RETURNS VarChar(max)
AS
BEGIN
DECLARE @TransTable TABLE(
   Rus Char 
  ,Lat VarChar(2)
 )INSERT @TransTable SELECT 'А','A'
  UNION ALL SELECT 'Б','B'
  UNION ALL SELECT 'В','V'
  UNION ALL SELECT 'Г','G'
  UNION ALL SELECT 'Д','D'
  UNION ALL SELECT 'Е','E'
  UNION ALL SELECT 'Ё','YO'
  UNION ALL SELECT 'Ж','ZH'
  UNION ALL SELECT 'З','Z'
  UNION ALL SELECT 'И','I'
  UNION ALL SELECT 'Й','Y'
  UNION ALL SELECT 'К','K'
  UNION ALL SELECT 'Л','L'
  UNION ALL SELECT 'М','M'
  UNION ALL SELECT 'Н','N'
  UNION ALL SELECT 'О','O'
  UNION ALL SELECT 'П','P'
  UNION ALL SELECT 'Р','R'
  UNION ALL SELECT 'С','S'
  UNION ALL SELECT 'Т','T'
  UNION ALL SELECT 'У','U'
  UNION ALL SELECT 'Ф','F'
  UNION ALL SELECT 'Х','H'
  UNION ALL SELECT 'Ц','C'
  UNION ALL SELECT 'Ч','CH'
  UNION ALL SELECT 'Ш','SH'
  UNION ALL SELECT 'Щ','SH'
  UNION ALL SELECT 'Ъ',''''
  UNION ALL SELECT 'Ы','Y'
  UNION ALL SELECT 'Ь',''''
  UNION ALL SELECT 'Э','E'
  UNION ALL SELECT 'Ю','YU'
  UNION ALL SELECT 'Я','YA'

 DECLARE @Result VarChar(max)
 SET @Result = @@String
 SELECT @Result = Replace(@Result,Upper(Rus) COLLATE Cyrillic_General_CS_AS,Upper(Lat)) FROM @TransTable WHERE @@String LIKE '%' + Rus + '%'
 SELECT @Result = Replace(@Result,Lower(Rus) COLLATE Cyrillic_General_CI_AS,Lower(Lat)) FROM @TransTable WHERE @@String LIKE '%' + Rus + '%'
 RETURN @Result
END

Вариант по по ГОСТ-а 7.79-2000

GO
/****** Object:  UserDefinedFunction [dbo].[TransLitGost]    Script Date: 05.04.2017 10:25:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[TransLitGost]
(
 @str VarChar(max)
)
RETURNS VarChar(max)
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

Источник:
http://www.sql.ru/forum/546853-2/est-li-u-kogo-gotovaya-funkciya-perevoda-translit-s-ruskogo-na-angliyskiy