oracle实用笔记.docVIP

判断数字，保留数字 select id,title,translate(zijin,-.0123456789||zijin,-.0123456789) from company where id in (select id from company where length(translate(zijin,-.0123456789||zijin,-.0123456789))!=length(zijin)) order by id; select id,title,zijin from company where id in (select id from company where length(translate(zijin,-.0123456789||zijin,-.0123456789))!=length(zijin)) order by id; select id,title,zijin,to_number(zijin),substr(zijin,0,instr(zijin,.)-1) from company where length(translate(trim(zijin),0123456789||zijin,0123456789))length(trim(zijin)); 去重复 select id,title from company where id not in(select min(id) from company group by title) select count(*) from company where id not in(select min(id) from company group by title) select count(*) from company where id in(select min(id) from company group by title) 查询重复数据sql: select id,title from company where title in(select title from company group by title having count(1)1) order by title asc update company set title= title||公司 select col from company where t like c% select count(*) from company where city like c% alter table p_admin rename column uid to id 全角半角 length(c.zijin)!=lengthb(c.zijin) SELECT To_single_byte(1あア2) FROM dual; ---转成半角 SELECT To_muil_byte(881898?71) FROM dual-----转成全角 全角/半角转换 if exists (select * from dbo.sysobjects where id = object_id(N[dbo].[f_convert]) and xtype in (NFN, NIF, NTF)) drop function [dbo].[f_convert] GO /*--全角/半角转换 转换说明 全角字符从的unicode编码从65281~65374 半角字符从的unicode编码从 33~126 空格比较特殊,全角为 12288,半角为 32 而且除空格外,全角/半角按unicode编码排序在顺序上是对应的 所以可以直接通过用+-法来处理非空格数据,对空格单独处理 like的时候,指定排序规则 COLLATE Latin1_General_BIN 是保证字符顺序按unicode编码排序 (此函数部分思路参考了CSDN上大力的转换函数) --邹建 2005.01(引用请保留此信息)--*/ /*--调用示例 declare @s1 varchar(8000) select @s1=中 ２-３４５６a７８ＳＴＵＶabｎ中国ｏｐｗｘｙｚ select dbo.f_convert(@s1,0),dbo.f_convert(@s1,1) --*/ create function f_convert( @str nvarchar(4000), --要转换的字符串 @flag bit --转换标志,0转换成半角,1转换成全角 )returns nvarchar(4000) as begin