mysql - 如何将SQL查询中的返回值从西里尔转换为拉丁

我有一张桌子，上面有汽车登记号码，
但有些登记号码是西里尔文。如果我搜索注册号“XX0000XX”，其中“X”是拉丁文，而在表中，一个或多个“X”是西里尔文，则找不到任何内容。
是否有编写此查询的方法，例如：

Select from cars where reg_num = 'XX0000XX'

以这样的方式，它也可以找到记录，包含西里尔字母和拉丁字母？Inwhere子句字符串是100%拉丁字母

最佳答案

如果使用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

那你的剧本

Select * from cars where TransLit(reg_num) = 'XX0000XX'

在'XX0000XX'中使用拉丁文。

关于mysql - 如何将SQL查询中的返回值从西里尔转换为拉丁，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46299144/