SQL拆卸

㈠ sql查询里 怎么拆分字符串（按“/”拆分）

先建立一个自定义函数，之个函数非常有用，建议收入自已的数据库
CREATE FUNCTION mysplit--将以某分隔符分段的字串,按指定的顺序号提取子串：

java">(@strnvarchar(2000),--源字串
@snint,--提取序号
@Delivarchar(1)--分隔符
)
RETURNSvarchar(100)
AS
BEGIN
declare@firstint,@lastint,@resultvarchar(1000),@sn0int
select@sn0=0,@first=0,@LAST=1,@str=@str+REPLICATE(@DELI,1)
while@sn0!=@sn
begin
select@sn0=@sn0+1,@first=@LAST,@last=charindex(@DELI,@str,@LAST)+1
end
if@last-@first-1<0
set@result=''
else
SET@RESULT=SUBSTRING(@str,@FIRST,@LAST-@FIRST-1)
RETURN(@RESULT)
END

查询方法：

DECLARE@AVARCHAR(100),@BVARCHAR(100),@CVARCHAR(100)
SELECT
@A=DBO.MYSPLIT('A|B|C',1,'|'),
@B=DBO.MYSPLIT('A|B|C',2,'|'),
@C=DBO.MYSPLIT('A|B|C',3,'|')
SELECT@A,@B,@C

㈡ SQL语句拆分表中内容，并变成两列

if exists (select 1 from sysobjects where name = 'uf_get_value' and xtype = 'FN')
drop function uf_get_value
go
create function uf_get_value(@value VARCHAR(100))
--获取列默认值
returns VARCHAR(100) as
begin
declare @rtn VARCHAR(100), @tmp VARCHAR(100), @status CHAR(1)

SET @rtn = ''
SET @tmp = ''
WHILE len(@value) > 0
BEGIN
IF ascii(substring(@value, 1, 1)) = ascii(',')
BEGIN
SET @value = RIGHT(@value, len(@value) - 1)
CONTINUE
END
IF ascii(substring(@value, 1, 1)) BETWEEN ascii('0') AND ascii('9')
BEGIN
SET @tmp = @tmp + substring(@value, 1, 1)
SET @status = 'N'
END
ELSE
BEGIN
SET @rtn = @rtn + substring(@value, 1, 1)
SET @status = 'S'
END
SET @value = RIGHT(@value, len(@value) - 1)
IF @value <> ''
BEGIN
IF @status = 'S' AND ascii(substring(@value, 1, 1)) BETWEEN ascii('0') AND ascii('9') SET @rtn = @rtn + ','
IF @status = 'N' AND NOT ascii(substring(@value, 1, 1)) BETWEEN ascii('0') AND ascii('9') SET @tmp = @tmp + ','
END
END
IF RIGHT(@rtn, 1) = ',' SET @rtn = LEFT(@rtn, len(@rtn) - 1)
SET @rtn = @rtn + '|' + @tmp
RETURN @rtn
END
go

--测试
--SELECT dbo.uf_get_value('A123,B23,BD21')

㈢ SQL如何进行表的拆分

1.复制表结构及数据到新表
create table 新表 select * from 旧表 where ...
2.只复制表结构到新表
create table 新表 select * from 旧表 where 1=2 (即:让where条件不成立)
3.复制旧表的数据到新表(假设两个表结构一样)
insert into 新表 select * from 旧表
4.复制旧表的数据到新表(假设两个表结构不一样)
insert into 新表(字段1,字段2,.......) select 字段1,字段2,...... from 旧表.

㈣ SQL拆分逗号分隔的字符串

1、首先点击新建查询按钮，新建一个查询。

㈤ sql数据拆分

第一个
FROM ccc A, # B

就该是
FROM ccc A, #临时表名 B