通过汉字得到首字母

在网上看到一个比较好的解决方案，特意摘录一下。

 

比如，

select dbo.f_dbo_zjm('中央人民广播电台')  -- 得到 ZYRMGBDT

select dbo.f_dbo_zjm('上海市浦东新区')  -- 得到 SHSPDXQ

 

以下是具体的做法,分3步进行:

 

1,建立测试表 tbTest，以后可以删除

 

if object_id('tbTest') is not null drop table tb
  go
create table tbTest(id int identity(1,1),spmch nvarchar(100))
 go
insert into tbTest(spmch)
select 
'丹黄祛瘀胶囊'         union all select
'复方甲硝唑阴道栓'     union all select
'平消胶囊'             union all select
'黄连上清丸'    union all select
'艽龙胶囊'  union all select
'注射用头孢噻肟钠'  union all select
'小儿消积止咳口服液'  union all select
'单硝酸异山梨酯片'  union all select
'心通口服液'  union all select
'阿胶胶囊'  union all select
'复方氯唑沙宗片'

2. 建立工具表 py_zjm 和工具函数 f_dbo_zjm ，不能删除

 

go
--得到汉字助记码
----a.生成汉字列表(GB2312)
--select top 71
--  ID=IDENTITY(int,176,1) into #a
--  from dbo.syscolumns a,dbo.syscolumns
--  
--select top 94
--  id=identity(int,161,1) into #b
--  from dbo.syscolumns a,dbo.syscolumns
--  
--
--select a.id,b.id,cast(a.id as binary(1)),cast(b.id as binary(1)),
--             ch=cast(
--               cast(a.id as BINARY(1))+cast(b.id as BINARY(1)) as CHAR(2)
--               ) collate chinese_prc_cs_as_ks_ws
--from #a a,#b b
--where not(a.id=215 and b.id between 250 and 254)
--order by ch
--
--drop table #a
--drop table #b
--http://www.knowsky.com/resource/gb2312tbl.htm 编码表

--create table py_zjm(zjm nchar(1) primary key,zjm1 nchar(1) collate chinese_prc_cs_as_ks_ws,
--                                                      zjm2 nchar(1) collate chinese_prc_cs_as_ks_ws)   
create table py_zjm(zjm nchar(1) primary key,zjm1 nchar(1),
                                                      zjm2 nchar(1))        --DROP TABLE PY_ZJM
insert py_zjm
SELECT 'A',N'吖',N'鏊'
UNION ALL SELECT 'B',N'八',N'簿'
UNION ALL SELECT 'C',N'嚓',N'错'
UNION ALL SELECT 'D',N'哒',N'跺'
UNION ALL SELECT 'E',N'屙',N'贰'
UNION ALL SELECT 'F',N'发',N'馥'
UNION ALL SELECT 'G',N'旮',N'过'
UNION ALL SELECT 'H',N'铪',N'蠖'
UNION ALL SELECT 'J',N'丌',N'竣'
UNION ALL SELECT 'K',N'咔',N'廓'
UNION ALL SELECT 'L',N'垃',N'雒'
UNION ALL SELECT 'M',N'妈',N'穆'
UNION ALL SELECT 'N',N'拿',N'糯'
UNION ALL SELECT 'O',N'噢',N'沤'
UNION ALL SELECT 'P',N'趴',N'曝'
UNION ALL SELECT 'Q',N'七',N'群'
UNION ALL SELECT 'R',N'蚺',N'箬'
UNION ALL SELECT 'S',N'仨',N'锁'
UNION ALL SELECT 'T',N'他',N'箨'
UNION ALL SELECT 'W',N'哇',N'鋈'
UNION ALL SELECT 'X',N'夕',N'蕈'
UNION ALL SELECT 'Y',N'丫',N'蕴'
UNION ALL SELECT 'Z',N'匝',N'做'

go
--  SELECT * FROM PY_ZJM
--得到助记码的函数
create function dbo.f_dbo_zjm
(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
  declare @i int 
  --取要查询的字中的第一个汉字
  set @i=patindex('%[吖-做]%' collate chinese_prc_cs_as_ks_ws,@str)
  while @i>0 
    select
      --将找到的第一个汉字用对应的拼音首字母替换
      @str=replace(@str,substring(@str,@i,1),zjm),
      --取下个要处理的汉字位置
      @i=patindex('%[吖-做]%' collate chinese_prc_cs_as_ks_ws,@str)
    from dbo.py_zjm
    where substring(@str,@i,1) between zjm1 and zjm2
  return(@str)
end

3, 得到首字母

-- 得到首字母
select id,spmch,dbo.f_dbo_zjm(spmch) as zjm from tbTest

select dbo.f_dbo_zjm('中央人民广播电台')  -- 得到 ZYRMGBDT

select dbo.f_dbo_zjm('上海市浦东新区')  -- 得到 SHSPDXQ