SQL按拼音字母查询指定字段的做法

最新推荐文章于 2024-03-27 18:37:22 发布

weixin_33688840

最新推荐文章于 2024-03-27 18:37:22 发布

阅读量151

点赞数

文章标签：数据库

SQL按拼音字母查询指定字段的做法

   前段时间要做一个按拼音字母的数据库查询，查了好多资料，总结了一个比较简单一点的方法，拿出来和大家分享。
   比如说我们要查到指定字段第一个字是以韵母“L”开头的，我们可以用以下SQL语句进行查询：
   SELECT * FROM 表名 WHERE author 所要查询的字段>='垃' AND 所要查询的字段 <'妈'
   这个语句就能查询出“所要查询的字段”的第一个字以“L”开头的所要数据。
   这查询方法的依据是某一个韵母在新华字典中的首个汉字和下一个韵母的第一个汉字作为查询的条件，进行查询。如果查询的时候要包含英文字母，可以使用下面的语句：
   SELECT * FROM 表名 WHERE author 所要查询的字段 LIKE 'L'   OR ( 所要查询的字段>='垃' AND 所要查询的字段 <'妈')
   特别说明一下当要查询“Z”的时候，查询的条件是“ >='杂' OR <'坐' ”
   下面是韵母和汉字的对照表：

a: 吖	b：巴	c: 擦	d: 搭	e: 鹅	f: 发	g: 旮
h: 哈	i:	j: 鸡	k: 喀	l: 垃	m: 妈	n: 嗯
o: 哦	p: 趴	q: 欺		r: 然	s: 仨	t: 他
u:	v:	w: 挖		x: 西	y: 压	z: 杂

函数

Code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_SearchWhere]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_SearchWhere]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_zhimu]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_zhimu]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE function f_SearchWhere(@Str nvarchar(200),@Col nvarchar(20))
returns  nvarchar(4000)
as
begin--函数实现开始

    --====================================
    --zh 2008-08-12
    --@Str nvarchar(200)--查询条件内容
    --@Col nvarchar(20)--查询条件列
    --====================================

    --自定变量
    declare @where nvarchar(4000)--返回查询条件变量
    declare @strLen int --传入的字串长度
    declare @i int--计数器
    declare @tmpstr  nvarchar(1)--字符缓存变量

    --变量初始化
    set @strLen=LEN(@Str)
    set @where=' where 1=1'
    set @i=0
    while(@i<@strLen)
    begin
        set @i=@i+1
        set @tmpstr=SUBSTRING(@Str,@i,1)

        if dbo.f_zhimu(@tmpstr)=1
        begin
        set @tmpstr=LOWER(@tmpstr)
        if @tmpstr='a'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''吖''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''巴'')'
        else if @tmpstr='b'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''巴''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''擦'')'
        else if  @tmpstr='c'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''擦''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''搭'')'
        else if  @tmpstr='d'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''搭''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''鹅'')'
        else if  @tmpstr='e'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''鹅''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''发'')'
        else if  @tmpstr='f'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''发''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''旮'')'
        else if  @tmpstr='g'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''旮''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''哈'')'
        else if  @tmpstr='h'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''哈''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''鸡'')'
        --else if  @tmpstr='i'
            --set @where=@where+' or '+@Col+ ' LIKE '''+@tmpstr+'%'''
        else if  @tmpstr='j'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''鸡''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''喀'')'
        else if  @tmpstr='k'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''喀''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''垃'')'
        else if  @tmpstr='l'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''垃''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''妈'')'
        else if  @tmpstr='m'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''妈''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''嗯'')'
        else if  @tmpstr='n'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''嗯''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''哦'')'
        else if  @tmpstr='o'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''哦''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''趴'')'
        else if  @tmpstr='p'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''趴''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''欺'')'
        else if  @tmpstr='q'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''欺''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''然'')'
        else if  @tmpstr='r'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''然''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''仨'')'
        else if  @tmpstr='s'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''仨''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''他'')'
        else if  @tmpstr='t'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''他''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''挖'')'
        --else if  @tmpstr='u'
            --set @where=@where+' or '+@Col+ ' LIKE '''+@tmpstr+'%'''
        --else if  @tmpstr='v'
            --set @where=@where+' or '+@Col+ ' LIKE '''+@tmpstr+'%'''
        else if  @tmpstr='w'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''挖''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''西'')'
        else if  @tmpstr='x'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''西''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''压'')'
        else if  @tmpstr='y'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''压''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''杂'')'
        else if  @tmpstr='z'
            set @where=@where+'  and  (SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)>=''杂''and SUBSTRING('+@Col+','+CAST(@i AS nvarchar)+',1)<''坐'')'
        end

    end
    set @where=@where+' and '+@Col+ ' LIKE '''+@Str+'%'''

return(@where)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE function f_zhimu(@Str nvarchar(1)='')

returns int
as
begin--函数实现开始
--=====================
--zh 2008-08-12
--=====================
declare @tmpflag int
if UNICODE(@Str)>=97 and UNICODE(@Str)<=122
    set @tmpflag=1
else
    if UNICODE(@Str)>=65 and UNICODE(@Str)<=90
        set @tmpflag=1
    else
        set @tmpflag=0
return(@tmpflag)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

过程