按条件创建视图

[叶落洛]

数据库现有表A中有一个字段，数据格式大致为 a_b_c_d_e_f_g_h_i
现需要生成一个视图，其中一列的内容为表A中的d，也就是将上面的字段以“_”分隔取第四个值，想问一下如何实现？

[卖水果的net]

create table test(c varchar(60))
go
insert into test values
('a_b_c_d_e_f_g_h_i'),('1a_2b_c_dd_e_f_g_h_i'),('111a_b_c_sd_e_f_g_h_i')
go
create view v1 
as 
with m as (
select convert(xml,'<n>' + replace(c,'_','</n><n>') + '</n>') c  from test 
)
select c.value('n[4]','varchar(30)') result from m 
go
select * from v1
go
drop table test
go
drop view v1
go


(3 行受影响)
result
------------------------------
d
dd
sd

(3 行受影响)

[吉普赛的歌]

/*
1. 先创建一个字符切分的表值函数 
*/
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL
	DROP FUNCTION [dbo].[Fun_Split]
GO
-- =============================================
-- Author     :	yenange
-- Create date: 2014-03-04
-- Description:	切分字符串
-- Example    : SELECT * FROM [dbo].[Fun_Split]('a,b,d,c',',')
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]  
(  
    @str    NVARCHAR(MAX),  
    @separator  NVARCHAR(MAX)  
)
RETURNS TABLE 
AS
RETURN
(
	SELECT ROW_NUMBER() OVER (order by (select 0)) AS rowNum, B.id  
	FROM   (
			SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')  
		   ) A  
		   OUTER APPLY(  
		SELECT id = N.v.value('.', 'nvarchar(max)')  
		FROM   A.[value].nodes('/v') N(v)  
	) B
	WHERE ISNULL(B.id,'')!=''
)
GO
/*
2. 调用就好了
*/
SELECT * FROM [dbo].[Fun_Split]('a_b_c_d_e_f_g_h_i','_') WHERE rowNum=4
/*
rowNum	id
4	    d
*/
/*
3. 创建测试表与视图
*/
IF OBJECT_ID('testTable') IS NOT NULL
	DROP TABLE dbo.testTable
GO
CREATE TABLE dbo.testTable(
	id INT IDENTITY(1,1) PRIMARY KEY,
	n NVARCHAR(50)	
)
INSERT INTO dbo.testTable(n) VALUES('a_b_c_d_e_f_g_h_i'),('a_b_z_x_e_f_g_h_i')
SELECT * FROM dbo.testTable AS tt
/*
id	n
1	a_b_c_d_e_f_g_h_i
2	a_b_z_x_e_f_g_h_i
*/
GO
IF OBJECT_ID('view_test') IS NOT NULL
	DROP VIEW view_test
GO
CREATE VIEW view_test
AS
	SELECT 
		*
		,(SELECT id FROM  dbo.Fun_Split(tt.n,'_') WHERE rowNum=4) AS fourth 
	FROM dbo.testTable AS tt
GO
SELECT * FROM view_test
/*
id	n	                fourth
1	a_b_c_d_e_f_g_h_i	d
2	a_b_z_x_e_f_g_h_i	x
*/