积木首页 - 学院 - 软件测试 - 技术百科 - 问答 - 黄页 - 手册 - 站长工具 - 社区
登录 | 注册
积木技术社区 » SqlServer » 关于拼音的sql语句
打印 楼主:yuanye136 发表于 2011-01-13 14:51 [回复30次/有效29个,浏览694次]

关于拼音的sql语句 [精]

我数据库里有一个字段是全由字母组成的 我想从里面搜索出来 那些是汉语拼音组成的 而且还有搜索出那些是双字拼音 那些是三字拼音。。。 研究了一下午都不行 那位高手会啊 帮帮忙 如果能实现多加分....
能帮帮忙想想办法的也不会白来.......
2楼 发表于 2011-01-13 14:52
我想要所有能用拼音可以读通的字段 那我............
3楼 发表于 2011-01-13 14:54
好像挺麻烦的
4楼 发表于 2011-01-13 15:12
哥 你真能吃! 也向你致敬一个!
5楼 发表于 2011-01-13 15:34
这个,把这些内容做到你一个表里面,然后使用not in ,not exists
6楼 发表于 2011-01-13 15:54
本想用递归的,但有问题,改用临时表.既然用了临时表,那干脆这样吧:
SQL code
create table tb(id int,col nvarchar(500))
insert into tb select 1,'adfs asdd vvvfews dsds shaocha sahao dddsv sdsds zhongguoren sddaaaaaaaafad'
insert into tb select 2,'fiusad nfiusd xing owesd ojoids nihao zhongdi chifan jiosdosad honglinjing foijasd'
create table sm(col varchar(2))
insert into sm
select 'zh' union all
select 'ch' union all
select 'sh' union all
select 'b' union all
select 'p' union all
select 'm' union all
select 'f' union all
select 'd' union all
select 't' union all
select 'n' union all
select 'l' union all
select 'g' union all
select 'k' union all
select 'h' union all
select 'j' union all
select 'q' union all
select 'x' union all
select 'r' union all
select 'z' union all
select 'c' union all
select 's' union all
select 'y' union all
select 'w'
create table ym(id int identity(1,1),col varchar(4))
insert into ym
select 'iang' union all
select 'uang' union all
select 'ueng' union all
select 'ang' union all
select 'eng' union all
select 'ing' union all
select 'ong' union all
select 'uai' union all
select 'uei' union all
select 'iou' union all
select 'ian' union all
select 'uan' union all
select 'van' union all
select 'uen' union all
select 'ia' union all
select 'ua' union all
select 'uo' union all
select 'ev' union all
select 'ai' union all
select 'ei' union all
select 'ui' union all
select 'ao' union all
select 'ou' union all
select 'iu' union all
select 'ie' union all
select 've' union all
select 'er' union all
select 'an' union all
select 'en' union all
select 'in' union all
select 'un' union all
select 'vn' union all
select 'a' union all
select 'o' union all
select 'e' union all
select 'i' union all
select 'u' union all
select 'v'
go
;with c1 as(
select id,1 as flg,convert(nvarchar(20),left(col,charindex(' ',col+' ')-1))col,convert(nvarchar(500),right(col+' ',len(col)-charindex(' ',col+' ')+1))Rcol from tb
union all
select id,flg+1 as flg,convert(nvarchar(20),left(Rcol,charindex(' ',Rcol)-1))col,convert(nvarchar(500),right(Rcol,len(Rcol)-charindex(' ',Rcol)+1))Rcol from c1 where len(Rcol)>1
),c2 as(
select a.id,a.flg,b.col+c.col as col,right(a.col,len(a.col)-len(b.col)-len(c.col))as Rcol
from c1 a,sm b,ym c
where a.col like b.col+c.col+'%'
and not exists(select 1 from c1,sm,ym where c1.col like sm.col+ym.col+'%' and c1.id=a.id and c1.flg=a.flg and ym.id
7楼 发表于 2011-01-13 16:04
你需求本身就没什么规律,等大牛或者做过这方面东西的人。
8楼 发表于 2011-01-13 16:29
降低一下要求 只要是有两个拼音组成 或者三个拼音组成的就行 不管组成的是什么 只有能拆分成两个或三个汉语拼音的就行 不含多余的字母就可以了 这样还容易点吧?
9楼 发表于 2011-01-13 16:54
嗯 就是没规律 所以才发愁啊 这样说吧 是上面所有的拼音组成的就选出来 这个能做到吗?
10楼 发表于 2011-01-13 17:25
不知有没有关于汉语拼音的正则
11楼 发表于 2011-01-13 17:26
这个涉及编译原理的内容了吧?
12楼 发表于 2011-01-13 17:52
干脆都用临时表得了,速度可以快很多!
SQL code
create table tb(id int,col nvarchar(500))
insert into tb select 1,'adfs asdd vvvfews dsds shaocha sahao dddsv sdsds zhongguoren sddaaaaaaaafad'
insert into tb select 2,'fiusad nfiusd xing owesd ojoids nihao zhongdi chifan jiosdosad honglinjing foijasd'
create table sm(col varchar(2))
insert into sm
select 'zh' union all
select 'ch' union all
select 'sh' union all
select 'b' union all
select 'p' union all
select 'm' union all
select 'f' union all
select 'd' union all
select 't' union all
select 'n' union all
select 'l' union all
select 'g' union all
select 'k' union all
select 'h' union all
select 'j' union all
select 'q' union all
select 'x' union all
select 'r' union all
select 'z' union all
select 'c' union all
select 's' union all
select 'y' union all
select 'w'
create table ym(id int identity(1,1),col varchar(4))
insert into ym
select 'iang' union all
select 'uang' union all
select 'ueng' union all
select 'ang' union all
select 'eng' union all
select 'ing' union all
select 'ong' union all
select 'uai' union all
select 'uei' union all
select 'iou' union all
select 'ian' union all
select 'uan' union all
select 'van' union all
select 'uen' union all
select 'ia' union all
select 'ua' union all
select 'uo' union all
select 'ev' union all
select 'ai' union all
select 'ei' union all
select 'ui' union all
select 'ao' union all
select 'ou' union all
select 'iu' union all
select 'ie' union all
select 've' union all
select 'er' union all
select 'an' union all
select 'en' union all
select 'in' union all
select 'un' union all
select 'vn' union all
select 'a' union all
select 'o' union all
select 'e' union all
select 'i' union all
select 'u' union all
select 'v'
go
;with c1 as(
select id,1 as flg,convert(nvarchar(20),left(col,charindex(' ',col+' ')-1))col,convert(nvarchar(500),right(col+' ',len(col)-charindex(' ',col+' ')+1))Rcol from tb
union all
select id,flg+1 as flg,convert(nvarchar(20),left(Rcol,charindex(' ',Rcol)-1))col,convert(nvarchar(500),right(Rcol,len(Rcol)-charindex(' ',Rcol)+1))Rcol from c1 where len(Rcol)>1
)select * into #1 from c1
select a.id,a.flg,b.col+c.col as col,right(a.col,len(a.col)-len(b.col)-len(c.col))as Rcol into #2
from #1 a,sm b,ym c
where a.col like b.col+c.col+'%'
and not exists(select 1 from #1,sm,ym where #1.col like sm.col+ym.col+'%' and #1.id=a.id and #1.flg=a.flg and ym.id
13楼 发表于 2011-01-13 18:32
能否给点这个字段的值的例子呢?
14楼 发表于 2011-01-13 18:36
达人们晚上加班了 现在都还没起床?
15楼 发表于 2011-01-13 19:03
这个...貌似涉及信号处理和模式识别的内容,研究生课程来的.
16楼 发表于 2011-01-13 19:07
晴天大哥威武 刚看鸭子的帖子了 晴天大哥确实威武 先不管结果 先致敬一下
17楼 发表于 2011-01-13 19:20
自己建个拼音库吧
18楼 发表于 2011-01-13 19:38
a,ai,an,ang,ao,ba,bai,ban,bang,bao,bei,ben,beng,bi,bian,biao,bie,bin,bing,bo,bu,ca,cai,can,cang,cao,ce,ceng,cha,chai,chan,chang,chao,che,chen,cheng,chi,chong,chou,chu,chuai,chuan,chuang,chui,chun,chuo,ci,cong,cou,cu,cuan,cui,cun,cuo,da,dai,dan,dang,dao,de,deng,di,dian,diao,die,ding,diu,dong,dou,du,duan,dui,dun,duo,e,en,er,fa,fan,fang,fei,fen,feng,fo,fou,fu,ga,gai,gan,gang,gao,ge,gei,gen,geng,gong,gou,gu,gua,guai,guan,guang,gui,gun,guo,ha,hai,han,hang,hao,he,hei,hen,heng,hong,hou,hu,hua,huai,huan,huang,hui,hun,huo,ji,jia,jian,jiang,jiao,jie,jin,jing,jiong,jiu,ju,juan,jue,jun,ka,kai,kan,kang,kao,ke,ken,keng,kong,kou,ku,kua,kuai,kuan,kuang,kui,kun,kuo,la,lai,lan,lang,lao,le,lei,leng,li,lia,lian,liang,liao,lie,lin,ling,liu,long,lou,lu,lv,luan,lue,lun,luo,ma,mai,man,mang,mao,me,mei,men,meng,mi,mian,miao,mie,min,ming,miu,mo,mou,mu,na,nai,nan,nang,nao,ne,nei,nen,neng,ni,nian,niang,niao,nie,nin,ning,niu,nong,nu,nv,nuan,nue,nuo,o,ou,pa,pai,pan,pang,pao,pei,pen,peng,pi,pian,piao,pie,pin,ping,po,pu,qi,qia,qian,qiang,qiao,qie,qin,qing,qiong,qiu,qu,quan,que,qun,ran,rang,rao,re,ren,reng,ri,rong,rou,ru,ruan,rui,run,ruo,sa,sai,san,sang,sao,se,sen,seng,sha,shai,shan,shang,shao,she,shen,sheng,shi,shou,shu,shua,shuai,shuan,shuang,shui,shun,shuo,si,song,sou,su,suan,sui,sun,suo,ta,tai,tan,tang,tao,te,teng,ti,tian,tiao,tie,ting,tong,tou,tu,tuan,tui,tun,tuo,wa,wai,wan,wang,wei,wen,weng,wo,wu,xi,xia,xian,xiang,xiao,xie,xin,xing,xiong,xiu,xu,xuan,xue,xun,ya,yan,yang,yao,ye,yi,yin,ying,yo,yong,you,yu,yuan,yue,yun,za,zai,zan,zang,zao,ze,zei,zen,zeng,zha,zhai,zhan,zhang,zhao,zhe,zhen,zheng,zhi,zhong,zhou,zhu,zhua,zhuai,zhuan,zhuang,zhui,zhun,zhuo,zi,zong,zou,zu,zuan,zui,zun,zuo
够不?
19楼 发表于 2011-01-13 19:45
提醒一下,楼主为何一定要把问题放在数据库领域里解决呢?
这个涉及较广,可以考虑交给前端程序来处理更为灵活和方便.
20楼 发表于 2011-01-13 20:01
没有。即使有 也是一组一组的拼音
标题:
内容:

PHP: PHP技巧 - PHP基础 - PHP实例 - PHP错误代码 - 正则表达式 - 热门脚本语言: DOS命令 - vbs - CSS/HTML - python - perl - ruby - shell编程 - Powershell

JS: node.js - JS基础 - extjs - jQuery - JavaScript技巧

网址导航成语大全积木 TinyMCE 服务邮箱: Gimoohr(#)gmail.com
Copyright © 2008 Gimoo.Net  京ICP备05050695号