Sql_Oracle字符串拆分

create table t3
(
a char(10),
b char(10),
c char(10),
d char(10)
)

create table te(a varchar(10) primary key not null,b int)
insert into te values('1',200)
insert into te values('',1)

select * from te
ALTER TABLE te
  ADD CONSTRAINT CK_b CHECK(b>=1)
GO
----------------------------------------------

begin transaction
declare @errorSum int
set @errorSum= 0
update te set b=b+200 where a='1'
set @errorSum=@errorSum+ 'select'+ convert(int,+'b')+' from te.b where a='+'2'
update te set b=b-200 where a='2'

print '查看事物结果'
select * from te
if @errorSum >1000
   begin
      print '交易失败'
      rollback transaction
      end
      else
        begin
           print '交易成功'
            commit transaction
        end
   go

create table tb(id int,姓名 varchar(30))
insert into tb values(1,'张三，李四a')
insert into tb values(2,'aaa，bbb')
select * from tb
go

 
--逻辑拆分字段值，substring()函数和charindex()函数，子查询
select A.id, 姓名 =substring(A.姓名, B.id,charindex(',', A.姓名 +',',B.id) -B.id)
from tb A,(select number as id from master..spt_values where type ='P') B
where substring(','+A.姓名, B.id, 1)=','
 
 
select id,姓名 = CHARINDEX(',',姓名,id) from tb

select A.id, 姓名 =substring(A.姓名, B.id, CHARINDEX(',',A.姓名+',',B.id)-B.id) 
from tb A,(select number as id from tb  where type='p') B
where substring(','+A.姓名,B.id, 1)=','
from master..spt_values

select id, CHARINDEX(',',姓名,id)  from tb
--得出结果是：  id 姓名
               --2  4 表示从第四个字符开始包含第四个
               --1  3

-- ','表示从这个字符开始计算截取 -id 表示从每个为id的姓名字段减1
select id,姓名 =substring(姓名, id, CHARINDEX(',',姓名)-id)  from tb
 
DECLARE @document varchar(64)

SELECT @document = 'Reflectors are vital safety' +
                   ' components of your bicycle.'
SELECT CHARINDEX('bicycle', @document) --‘bicycle’参数表示计算整个字符串的截止的字符串
GO                                     --@document计算的表达式

--Oracle的拆分处理方法将'_'符号拆分出来
--select id,substr(name,1,instr(name,'_')-1),substr(name,instr(name,'_')+1) from tableName

--select substr(to_char(date,'YYYY-MM-DD HH24:mi:ss'),1,10) 日期,
--substr(to_char(date,'YYYY-MM-DD HH24:mi:ss'),12) 时间,
--substr(name,1,2) 字符1,
--substr(name,4,2) 字符2,
--substr(name,7,2) 字符3
--from tablename