如何从列中分别检索值？ | 如何从列中分别检索值

本文介绍了如何从列中分别检索值？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

In database i saved multiple values in one column.
For example:-Column name-Shopping
             Values-jabong,amazon,snapdeal



Now i want to retrieve these values separately(Jabong separate in grid view column or check box,amazon separate,snapdeal separate) and display it as links.How can i do this?</pre>

推荐答案

CREATE   FUNCTION [dbo].[fn_Split]
 (@List  VARCHAR(8000), @Delimiter CHAR(1))

RETURNS @Results TABLE
 (Item VARCHAR(8000),ID INT IDENTITY(1,1))

AS

BEGIN
 DECLARE @IndexStart INT
 DECLARE @IndexEnd INT
 DECLARE @Length  INT
 DECLARE @Word  VARCHAR(8000)

 SET @IndexStart = 1
 SET @IndexEnd = 0

 SET @Length = LEN(@List)
IF @Delimiter = '' SET @Delimiter = ','

--Get rid of any tabs or returns
SET @List = REPLACE(@List,CHAR(9),'')
SET @List = REPLACE(@List,CHAR(10),'')
SET @List = REPLACE(@List,CHAR(13),'')

WHILE @IndexStart <= @Length
BEGIN
	SET @IndexEnd = CHARINDEX(@Delimiter, @List, @IndexStart)
		
	IF @Delimiter = CHAR(32) 
		SET @IndexEnd = CHARINDEX(SPACE(1), @List, @IndexStart)

	IF @IndexEnd = 0
		SET @IndexEnd = @Length + 1

	SET @Word = SUBSTRING(@List, @IndexStart, @IndexEnd - @IndexStart)
	SET @IndexStart = @IndexEnd + 1

	INSERT INTO @Results(Item)
	SELECT @Word
END

RETURN
END 

GO

用法

Usage

DECLARE @VALUES VARCHAR(1000)

SET @Values = 'Shop1,Shop2,Shop3,Shop4,Shop5,Shop6,Shop7 ' 


SELECT 1001 AS PrimaryKey, item AS Vendor
from dbo.fn_Split(@VALUES,',')

这篇关于如何从列中分别检索值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！