Question

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

如何通过sql语句，把一列中的数据读出来，并把字母和数字分开，变成两列，比如：列名“Column1”，里面有若干含A123,B23,BD21这种字母加数字的数据，如何把它们分成两列成为A,B,BD|123,23,21这样子的

Answer 1

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')

Answer 2

USE tempdb 
GO
ALTER FUNCTION DBO.SPLT 
(@COL NVARCHAR(200),
@PRA NVARCHAR(10)
)
RETURNS NVARCHAR(100)
AS

BEGIN
	DECLARE @NUM NVARCHAR(100),@STR NVARCHAR(100),@I INT 
	SET @NUM=''
	SET @STR=''
	SET @I=1

	WHILE(@I<=LEN(ISNULL(@COL,'')))
		BEGIN
			IF  SUBSTRING(@COL,@I,1)IN('0','1','2','3','4','5','6','7','8','9') --说明是数字
				SET @NUM=@NUM+SUBSTRING(@COL,@I,1)
			ELSE
				SET @STR=@STR+SUBSTRING(@COL,@I,1)
			SET @I=@I+1
			
		END
	IF @PRA='STR'
		SET @NUM=@STR
	RETURN @NUM
END
GO
SELECT dbo.SPLT('A123,B23,BD21','STR'),dbo.SPLT('A123,B23,BD21','')

Answer 3

用substring()函数 分割字符串 或者 like 正则匹配

Answer 4

用excel做一个表结构，然后把想等到的结果放上来