oracle实用笔记

《oracle实用笔记》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、判断数字，保留数字selectid,title,translate(zijin,'-.0123456789'

2、

3、zijin,'-.0123456789')fromcompanywhereidin(selectidfromcompanywherelength(translate(zijin,'-.0123456789'

4、

5、zijin,'-.0123456789'))!=length(zijin))orderbyid;selectid,title,zijinfromcompanywhereidin(selectidfromcompanywher

6、elength(translate(zijin,'-.0123456789'

7、

8、zijin,'-.0123456789'))!=length(zijin))orderbyid;selectid,title,zijin,to_number(zijin),substr(zijin,0,instr(zijin,'.')-1)fromcompanywherelength(translate(trim(zijin),'0123456789'

9、

10、zijin,'0123456789'))<>length(trim(zijin));去重复selectid,

11、titlefromcompanywhereidnotin(selectmin(id)fromcompanygroupbytitle)selectcount(*)fromcompanywhereidnotin(selectmin(id)fromcompanygroupbytitle)selectcount(*)fromcompanywhereidin(selectmin(id)fromcompanygroupbytitle)查询重复数据sql:selectid,titlefromcompanywheretitlein(selecttitlef

12、romcompanygroupbytitlehavingcount(1)>1)orderbytitleascupdatecompanysettitle=title

13、

14、'公司'select&colfromcompanywhere&tlike'&c%'selectcount(*)fromcompanywherecitylike'&c%'altertablep_adminrenamecolumn"uid"toid全角半角length(c.zijin)!=lengthb(c.zijin)SELECTTo_single_byte('1あア2')FRO

15、Mdual;---转成半角SELECTTo_muil_byte('881898?71')FROMdual-----转成全角全角/半角转换ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_convert]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_convert]GO/*--全角/半角转换转换说明全角字符从的unicode编码从65281~65374半角字符从的unicode编码从33~126空格比较特殊,

16、全角为12288,半角为32而且除空格外,全角/半角按unicode编码排序在顺序上是对应的所以可以直接通过用+-法来处理非空格数据,对空格单独处理like的时候,指定排序规则COLLATELatin1_General_BIN是保证字符顺序按unicode编码排序(此函数部分思路参考了CSDN上大力的转换函数)--邹建2005.01(引用请保留此信息)--*//*--调用示例declare@s1varchar(8000)select@s1='中２-３４５６a７８ＳＴＵＶabｎ中国ｏｐｗｘｙｚ'selectdbo.f_convert(@s1,

17、0),dbo.f_convert(@s1,1)--*/createfunctionf_convert(@strnvarchar(4000),--要转换的字符串@flagbit--转换标志,0转换成半角,1转换成全角)returnsnvarchar(4000)asbegindeclare@patnvarchar(8),@stepint,@iint,@spcintif@flag=0select@pat=N'%[！-～　]%',@step=-65248elseselect@pat=N'%[!-~]%',@step=65248set@i=patin

18、dex(@patCOLLATELatin1_General_BIN,@str)while@i>0select@str=stuff(@str,@i,1,nchar(caseunic