有一个表,字段内容是汉字+英文, 如“创世纪Genisis” ,我想把这个字段分成两个字段存储,一个存汉字,一个存英文。
我用sqlite3 dbbuilder 如何写SQL 来作这事 ?找了半天没找到相关函数, 请教大侠。
我有一个Sqlite3表字段内容是汉字+英文,我如何用SQL语句分成两个字段
答案:1 悬赏:30
解决时间 2021-12-11 22:33
- 提问者网友:陪我到最后
- 2021-12-11 16:26
最佳答案
- 二级知识专家网友:萌萌哒小可爱
- 2021-12-11 16:51
create table t
(
id int,
sqlite varchar(2000)
)
insert into t(id ,sqlite ) values (1,'你好SQL');
insert into t(id ,sqlite ) values (2,'再见地球');
insert into t(id ,sqlite ) values (3,'创世纪Genisis');
insert into t(id ,sqlite ) values (4,'SELECt语句');
insert into t(id ,sqlite ) values (5,'SELECT');
CREATE FUNCTION [dbo].[fz]
(
@s varchar(2000) =''
)
returns varchar(2000)
as
begin
if ascii(@s) is null
return ''
IF ascii(left(@s,1))>123
begin
declare @i int
set @i = 1
declare @ss varchar(100)
set @ss =SUBSTRING (@s,@i,1)
while (ASCII (@ss)>123)
begin
set @i=@i+1
set @ss=SUBSTRING (@s,@i,1)
end
return substring(@s,1,@i-1)
end
IF ascii(left(@s,1))<=123 and ascii(left(@s,1))>0
BEGIN
declare @j int
set @j = 1
declare @sa varchar(1000)
set @sa =SUBSTRING (@s,@j,1)
while (ASCII (@sa)<=123)
begin
set @j=@j+1
set @sa=SUBSTRING (@s,@j,1)
end
return substring(@s,1,@j-1)
end
return @s
end
go
select ID,sqlite,dbo.fz(sqlite) as 汉字部分,REPLACE (sqlite,dbo.fz(sqlite),'') 英文部分 from t
(
id int,
sqlite varchar(2000)
)
insert into t(id ,sqlite ) values (1,'你好SQL');
insert into t(id ,sqlite ) values (2,'再见地球');
insert into t(id ,sqlite ) values (3,'创世纪Genisis');
insert into t(id ,sqlite ) values (4,'SELECt语句');
insert into t(id ,sqlite ) values (5,'SELECT');
CREATE FUNCTION [dbo].[fz]
(
@s varchar(2000) =''
)
returns varchar(2000)
as
begin
if ascii(@s) is null
return ''
IF ascii(left(@s,1))>123
begin
declare @i int
set @i = 1
declare @ss varchar(100)
set @ss =SUBSTRING (@s,@i,1)
while (ASCII (@ss)>123)
begin
set @i=@i+1
set @ss=SUBSTRING (@s,@i,1)
end
return substring(@s,1,@i-1)
end
IF ascii(left(@s,1))<=123 and ascii(left(@s,1))>0
BEGIN
declare @j int
set @j = 1
declare @sa varchar(1000)
set @sa =SUBSTRING (@s,@j,1)
while (ASCII (@sa)<=123)
begin
set @j=@j+1
set @sa=SUBSTRING (@s,@j,1)
end
return substring(@s,1,@j-1)
end
return @s
end
go
select ID,sqlite,dbo.fz(sqlite) as 汉字部分,REPLACE (sqlite,dbo.fz(sqlite),'') 英文部分 from t
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯