如何从列中分别检索值？ [英] How to retrieve values separately from column?

问题描述

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>

推荐答案

您正在支付不良数据库设计的成本。值应该在单独的记录中，而不是卡在一个字段中。



您可以提取记录，然后使用拆分功能在业务逻辑层中创建子集合。



您可以在数据库中创建一个执行相同操作的子查询，我建议您使用2个字段，主键和每个记录的一个值使用拆分功能。



旧的拆分功能

You are paying the cost of bad database design. The values should be in separate records and not jammed into one field.

You could extract the record and then create a child collection in your business logic layer using a split function.

You could create a sub query in the database that does the same thing, I suggest you use 2 fields, the primary key and ONE of the values per record using a split function.

An old split function

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

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