原始表
希望的效果
12 个解决方案
#1
新建函数:
查询语句:
CREATE FUNCTION dbo.get2str
(
@SourceSql NVARCHAR(MAX)
)
RETURNS @temp TABLE ( F1 VARCHAR(100) )
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
WHILE ( @SourceSql <> '' )
BEGIN
SET @ch = LEFT(@SourceSql,2)
INSERT @temp
VALUES ( @ch )
SET @SourceSql = STUFF(@SourceSql, 1,2,
'')
END
RETURN
END
GO
查询语句:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([DM] int,[MC] nvarchar(23),[QX] nvarchar(30))
Insert #T
select 1,N'一病区',N'Z1' union all
select 2,N'三病区',N'h3hzz1c3y1'
Go
--测试数据结束
SELECT DM ,
MC ,
f1 AS QX
FROM #T
CROSS APPLY ( SELECT *
FROM dbo.get2str(QX)
) t
#2
辛苦大神,但是我在函数新建之后,直接将“测试数据后”的那些语句应用到zybq表上,一直报“SQL命令未正确结束”的错误,是为什么?非常感谢解答
#3
新建函数:
查询语句:
是mssql吗?
CREATE FUNCTION dbo.get2str
(
@SourceSql NVARCHAR(MAX)
)
RETURNS @temp TABLE ( F1 VARCHAR(100) )
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
WHILE ( @SourceSql <> '' )
BEGIN
SET @ch = LEFT(@SourceSql,2)
INSERT @temp
VALUES ( @ch )
SET @SourceSql = STUFF(@SourceSql, 1,2,
'')
END
RETURN
END
GO
查询语句:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([DM] int,[MC] nvarchar(23),[QX] nvarchar(30))
Insert #T
select 1,N'一病区',N'Z1' union all
select 2,N'三病区',N'h3hzz1c3y1'
Go
--测试数据结束
SELECT DM ,
MC ,
f1 AS QX
FROM #T
CROSS APPLY ( SELECT *
FROM dbo.get2str(QX)
) t
是mssql吗?
#4
Oracle
#5
给移动Oracle版了,mssql和Oracle有的还是不太一样。
#6
好的,非常感谢
#7
with tmp as
(
select 1 dm, '一病区(老院)' mc, 'Z1' qx from dual union all
select 2 dm, '二病区(老院)' mc, 'Z1' qx from dual union all
select 3 dm, '三病区' mc, 'h3hzZ1c3y1' qx from dual union all
select 4 dm, '四病区' mc, 'h4hzZ1c3y1' qx from dual
)
select distinct dm,mc,qx,lv from(
select dm,mc,substr(qx,(level-1)*2+1,2 ) qx,level lv
from tmp
connect by level <= (select max(round( length(qx)/2)) from tmp)
)where qx is not null
order by dm,lv
用我这个SQL语句试试看。
#8
如果最终结果不需要排序的话,就不用查询lv字段,。
这个字段我是用来辅助排序的。
这个字段我是用来辅助排序的。
#9
可以实现,真的是太感谢,谢谢大神
#10
共同学习,一起进步~
#11
12c
with tmp as
(
select 1 dm, '一病区(老院)' mc, 'Z1' qx from dual union all
select 2 dm, '二病区(老院)' mc, 'Z1' qx from dual union all
select 3 dm, '三病区' mc, 'h3hzZ1c3y1' qx from dual union all
select 4 dm, '四病区' mc, 'h4hzZ1c3y1' qx from dual
)
select dm,mc,regexp_substr(qx,'.{2}',1,l) as qx
from tmp t
outer apply(select level as l from dual connect by level<=length(t.qx)/2)
with tmp as
(
select 1 dm, '一病区(老院)' mc, 'Z1' qx from dual union all
select 2 dm, '二病区(老院)' mc, 'Z1' qx from dual union all
select 3 dm, '三病区' mc, 'h3hzZ1c3y1' qx from dual union all
select 4 dm, '四病区' mc, 'h4hzZ1c3y1' qx from dual
)
select dm,mc,regexp_substr(qx,'.{2}',1,l) as qx
from tmp t
outer apply(select level as l from dual connect by level<=length(t.qx)/2)
#12
楼主,我试试下手机移动端回帖,听说可用分加倍~
#1
新建函数:
查询语句:
CREATE FUNCTION dbo.get2str
(
@SourceSql NVARCHAR(MAX)
)
RETURNS @temp TABLE ( F1 VARCHAR(100) )
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
WHILE ( @SourceSql <> '' )
BEGIN
SET @ch = LEFT(@SourceSql,2)
INSERT @temp
VALUES ( @ch )
SET @SourceSql = STUFF(@SourceSql, 1,2,
'')
END
RETURN
END
GO
查询语句:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([DM] int,[MC] nvarchar(23),[QX] nvarchar(30))
Insert #T
select 1,N'一病区',N'Z1' union all
select 2,N'三病区',N'h3hzz1c3y1'
Go
--测试数据结束
SELECT DM ,
MC ,
f1 AS QX
FROM #T
CROSS APPLY ( SELECT *
FROM dbo.get2str(QX)
) t
#2
辛苦大神,但是我在函数新建之后,直接将“测试数据后”的那些语句应用到zybq表上,一直报“SQL命令未正确结束”的错误,是为什么?非常感谢解答
#3
新建函数:
查询语句:
是mssql吗?
CREATE FUNCTION dbo.get2str
(
@SourceSql NVARCHAR(MAX)
)
RETURNS @temp TABLE ( F1 VARCHAR(100) )
AS
BEGIN
DECLARE @ch AS VARCHAR(100)
WHILE ( @SourceSql <> '' )
BEGIN
SET @ch = LEFT(@SourceSql,2)
INSERT @temp
VALUES ( @ch )
SET @SourceSql = STUFF(@SourceSql, 1,2,
'')
END
RETURN
END
GO
查询语句:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([DM] int,[MC] nvarchar(23),[QX] nvarchar(30))
Insert #T
select 1,N'一病区',N'Z1' union all
select 2,N'三病区',N'h3hzz1c3y1'
Go
--测试数据结束
SELECT DM ,
MC ,
f1 AS QX
FROM #T
CROSS APPLY ( SELECT *
FROM dbo.get2str(QX)
) t
是mssql吗?
#4
Oracle
#5
给移动Oracle版了,mssql和Oracle有的还是不太一样。
#6
好的,非常感谢
#7
with tmp as
(
select 1 dm, '一病区(老院)' mc, 'Z1' qx from dual union all
select 2 dm, '二病区(老院)' mc, 'Z1' qx from dual union all
select 3 dm, '三病区' mc, 'h3hzZ1c3y1' qx from dual union all
select 4 dm, '四病区' mc, 'h4hzZ1c3y1' qx from dual
)
select distinct dm,mc,qx,lv from(
select dm,mc,substr(qx,(level-1)*2+1,2 ) qx,level lv
from tmp
connect by level <= (select max(round( length(qx)/2)) from tmp)
)where qx is not null
order by dm,lv
用我这个SQL语句试试看。
#8
如果最终结果不需要排序的话,就不用查询lv字段,。
这个字段我是用来辅助排序的。
这个字段我是用来辅助排序的。
#9
可以实现,真的是太感谢,谢谢大神
#10
共同学习,一起进步~
#11
12c
with tmp as
(
select 1 dm, '一病区(老院)' mc, 'Z1' qx from dual union all
select 2 dm, '二病区(老院)' mc, 'Z1' qx from dual union all
select 3 dm, '三病区' mc, 'h3hzZ1c3y1' qx from dual union all
select 4 dm, '四病区' mc, 'h4hzZ1c3y1' qx from dual
)
select dm,mc,regexp_substr(qx,'.{2}',1,l) as qx
from tmp t
outer apply(select level as l from dual connect by level<=length(t.qx)/2)
with tmp as
(
select 1 dm, '一病区(老院)' mc, 'Z1' qx from dual union all
select 2 dm, '二病区(老院)' mc, 'Z1' qx from dual union all
select 3 dm, '三病区' mc, 'h3hzZ1c3y1' qx from dual union all
select 4 dm, '四病区' mc, 'h4hzZ1c3y1' qx from dual
)
select dm,mc,regexp_substr(qx,'.{2}',1,l) as qx
from tmp t
outer apply(select level as l from dual connect by level<=length(t.qx)/2)
#12
楼主,我试试下手机移动端回帖,听说可用分加倍~