一个取字符串并进行对比的问题

有如下数据
Id    Property
1     A:10|B:5|C:25
2     A:3|B:6|C:12
3     A:5|B:10|C:22
4     A:12|B:6|C:23
5     B:1|A:10|C:22
6     C:10|B:10|A:10
...
数据ABC是以键值组成字符串并有:和|作为分隔符保存到varchar字段里
我想查询出这样的结果。
在Property字段中B字符串对应的数字大于1的都取出来，取出来的数据包含ABC三个属性的值。
想了半天，写了半天，只能把B对应的数字取出来，却不能对比大小。头疼啊，请大家帮忙。

16 个解决方案

#1

以下是我的代码：

select substring(INP1,1,charindex('|',INP1)-1) as INP2 from (select right(Property,len(Property)-charindex('Rank:',Property)-4) as INP1 from Info where Property <> '') a

#2

B在第几个|中不固定哦?

#3

是啊。B的位置不固定的。

#4

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID INT,
[PROPERTY] VARCHAR(500)
)
INSERT INTO TB
SELECT 1    ,'A:10|B:5|C:25' UNION ALL
SELECT 2    ,'A:3|B:6|C:12' UNION ALL
SELECT 3    ,'A:5|B:10|C:22' UNION ALL
SELECT 4    ,'A:12|B:6|C:23' UNION ALL
SELECT 5    ,'B:1|A:10|C:22' UNION ALL
SELECT 6    ,'C:10|B:10|A:10' 
SELECT ID,[PROPERTY]+'|'
,LEFT(STUFF([PROPERTY]+'|',1,CHARINDEX('B',[PROPERTY])+1,'')
,CHARINDEX('|',STUFF([PROPERTY]+'|',1,CHARINDEX('B',[PROPERTY])+1,''))-1)
FROM TB
WHERE CAST(LEFT(STUFF([PROPERTY]+'|',1,CHARINDEX('B',[PROPERTY])+1,'')
,CHARINDEX('|',STUFF([PROPERTY]+'|',1,CHARINDEX('B',[PROPERTY])+1,''))-1) AS NUMERIC(19,6))>1
/*
1	A:10|B:5|C:25|	5
2	A:3|B:6|C:12|	6
3	A:5|B:10|C:22|	10
4	A:12|B:6|C:23|	6
6	C:10|B:10|A:10|	10
*/

#5

罪过罪过 又没忍住抢分了。。。。

#6

DECLARE @tb TABLE(Id int,Property varchar(8000))
INSERT @tb 
SELECT 1,'A:10|B:5|C:25' UNION ALL 
SELECT 2,'A:3|B:6|C:12'  UNION ALL 
SELECT 3,'A:5|B:10|C:22'  UNION ALL 
SELECT 4,'A:12|B:6|C:23'  UNION ALL 
SELECT 5,'B:1|A:10|C:22'  UNION ALL 
SELECT 6,'C:10|B:10|A:10'  

SELECT *
FROM (
    SELECT id,Property,
        PARSENAME(REPLACE(Property,'|','.'),1) AS f,
        PARSENAME(REPLACE(Property,'|','.'),2) AS s,
        PARSENAME(REPLACE(Property,'|','.'),3) AS t
    FROM @tb
) AS T
WHERE CHARINDEX('B',f) > 0 AND CAST(RIGHT(f,CHARINDEX(':',REVERSE(f))-1) AS int) > 1
   OR CHARINDEX('B',s) > 0 AND CAST(RIGHT(s,CHARINDEX(':',REVERSE(s))-1) AS int) > 1
   OR CHARINDEX('B',t) > 0 AND CAST(RIGHT(t,CHARINDEX(':',REVERSE(t))-1) AS int) > 1

/*
id	Property	f	s	t
1	A:10|B:5|C:25	C:25	B:5	A:10
2	A:3|B:6|C:12	C:12	B:6	A:3
3	A:5|B:10|C:22	C:22	B:10	A:5
4	A:12|B:6|C:23	C:23	B:6	A:12
6	C:10|B:10|A:10	A:10	B:10	C:10
*/

#7

LiangCk，我用你的代码修改后，查询出来的结果是空的。没有数据。怎么回事呢

SELECT *
FROM (
    SELECT Idol_NPC_Property,
        PARSENAME(REPLACE(Idol_NPC_Property,'|','.'),1) AS f,
        PARSENAME(REPLACE(Idol_NPC_Property,'|','.'),2) AS s,
        PARSENAME(REPLACE(Idol_NPC_Property,'|','.'),3) AS t
    FROM Idol_NPCInfo
) AS T
WHERE CHARINDEX('Rank',f) > 0 AND CAST(RIGHT(f,CHARINDEX(':',REVERSE(f))-1) AS int) > 1
   OR CHARINDEX('Rank',s) > 0 AND CAST(RIGHT(s,CHARINDEX(':',REVERSE(s))-1) AS int) > 1
   OR CHARINDEX('Rank',t) > 0 AND CAST(RIGHT(t,CHARINDEX(':',REVERSE(t))-1) AS int) > 1

#8

噢，对了。Property字段里面元素个数不定的，不好意思忘记说了，有时候是ABCDEFG，有时候是ABC

#9

UP

#10

DECLARE @tb TABLE(Id int,Property varchar(100))
INSERT @tb 
SELECT 1,'A:10|B:5|C:25' UNION ALL 
SELECT 2,'A:3|B:6|C:12'  UNION ALL 
SELECT 3,'A:5|B:10|C:22'  UNION ALL 
SELECT 4,'A:12|B:6|C:23'  UNION ALL 
SELECT 5,'B:1|A:10|C:22'  UNION ALL 
SELECT 6,'C:10|B:10|A:10'  

--SQL查询如下:

DECLARE @Property varchar(10);
SET @Property = 'B'

;WITH Liang AS
(
    SELECT
        id,Property,
        CONVERT(xml,'<v><![CDATA['
                +REPLACE(Property,'|',']]></v><v><![CDATA[')+']]></v>') AS xml_doc
    FROM @tb
),
Liang2 AS 
(
    SELECT A.id,A.Property,B.doc
    FROM Liang AS A
        CROSS APPLY(
            SELECT doc =(
                       SELECT 
                           LEFT(T.x.value('.','varchar(10)'),
                                  CHARINDEX(':',T.x.value('.','varchar(10)'))-1) AS [@name],
                           RIGHT(T.x.value('.','varchar(10)'),
                                  CHARINDEX(':',REVERSE(T.x.value('.','varchar(10)')))-1) AS value
                       FROM A.xml_doc.nodes('//v') AS T(x)
                       FOR XML PATH('row'),TYPE
                   )
        ) AS B 
)
SELECT id,Property FROM Liang2
WHERE doc.exist('/row[@name=sql:variable("@Property")]/value[. > 1]') = 1;


/*
id          Property
----------- ------------------------
1           A:10|B:5|C:25
2           A:3|B:6|C:12
3           A:5|B:10|C:22
4           A:12|B:6|C:23
6           C:10|B:10|A:10

(5 行受影响)

*/

#11

DECLARE @tb TABLE(Id int,Property varchar(100))
INSERT @tb 
SELECT 1,'A:10|B:5|C:25' UNION ALL 
SELECT 2,'A:3|B:6|C:12|E:6|C:12|F:6|G:12'  UNION ALL 
SELECT 3,'A:5|B:10|C:22|C:10|Rank:22'  UNION ALL 
SELECT 4,'A:12|Rank:6|C:23'  UNION ALL 
SELECT 5,'B:1|A:10|E:22|V:10|J:22|T:10|U:22|Rank:10|L:22'  UNION ALL 
SELECT 6,'C:10|B:10|A:10'  

--SQL查询如下:

DECLARE @Property varchar(10);
SET @Property = 'Rank'

;WITH Liang AS
(
    SELECT
        id,Property,
        CONVERT(xml,'<v><![CDATA['
                +REPLACE(Property,'|',']]></v><v><![CDATA[')+']]></v>') AS xml_doc
    FROM @tb
),
Liang2 AS 
(
    SELECT A.id,A.Property,B.doc
    FROM Liang AS A
        CROSS APPLY(
            SELECT doc =(
                       SELECT 
                           LEFT(T.x.value('.','varchar(10)'),
                                  CHARINDEX(':',T.x.value('.','varchar(10)'))-1) AS [@name],
                           RIGHT(T.x.value('.','varchar(10)'),
                                  CHARINDEX(':',REVERSE(T.x.value('.','varchar(10)')))-1) AS value
                       FROM A.xml_doc.nodes('//v') AS T(x)
                       FOR XML PATH('row'),TYPE
                   )
        ) AS B 
)
SELECT id,Property FROM Liang2
WHERE doc.exist('/row[@name=sql:variable("@Property")]/value[xs:float(.) > 1]') = 1;


/*
id          Property
----------- ------------------------
3           A:5|B:10|C:22|C:10|Rank:22
4           A:12|Rank:6|C:23
5           B:1|A:10|E:22|V:10|J:22|T:10|U:22|Rank:10|L:22

(3 行受影响)

*/

#12

--创建一个分解字符串的函数
CREATE FUNCTION test_fun
(
    @string     VARCHAR(1000)
)
RETURNS @tempTable TABLE (code VARCHAR(10),num INT)
AS
BEGIN
    DECLARE @temp   VARCHAR(1000)
    
    IF RTRIM(@string) <> '' AND @string IS NOT NULL
    BEGIN
        WHILE CHARINDEX('|',@string) > 0
        BEGIN
            SET @temp = LEFT(@string,CHARINDEX('|',@string))
            INSERT INTO @tempTable ( code, num ) 
            VALUES (SUBSTRING(@temp,1,CHARINDEX(':',@temp) - 1),SUBSTRING(REPLACE(@temp,'|',''),CHARINDEX(':',@temp) + 1,100))
            
            SET @string = SUBSTRING(@string,CHARINDEX('|',@string) + 1,1000)
        END
        
        INSERT INTO @tempTable ( code, num ) 
        VALUES (SUBSTRING(@string,1,CHARINDEX(':',@string) - 1),SUBSTRING(REPLACE(@string,'|',''),CHARINDEX(':',@string) + 1,100))
    END
    RETURN
END
GO

--测试
DECLARE @tb TABLE(Id int,Property varchar(8000))
INSERT @tb 
SELECT 1,'A:10|B:5|C:25' UNION ALL 
SELECT 2,'A:3|B:6|C:12'  UNION ALL 
SELECT 3,'A:5|B:10|C:22'  UNION ALL 
SELECT 4,'A:12|B:6|C:23'  UNION ALL 
SELECT 5,'B:1|A:10|C:22'  UNION ALL 
SELECT 6,'C:10|B:10|A:10' UNION ALL  
SELECT 7,'E:100|B:10|A:9' UNION ALL 
SELECT 8,'X:11|Y:10|Z:12|B:99|A:10|C:22' 


SELECT * FROM @tb A
WHERE EXISTS(SELECT * FROM dbo.test_fun(A.Property) B WHERE B.code = 'B' AND B.num >= 1) --存在B,且B>1
AND CHARINDEX('A',A.Property) > 0 --并且存在A
AND CHARINDEX('C',A.Property) > 0 --并且存在C
GO

#13

SQL语句可以实现，不过说实话， 我自己看得都是晕的！！ 你还是听楼上的， 创建个函数来实现吧。

 select *,
	(
	case charindex('|',substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property])))
		when 0 then substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property]))
		else left(substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property]))
			,charindex('|',substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property]))) -1
			)
	end
	) as v
from tb
where
cast(
	(
	case charindex('|',substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property])))
		when 0 then substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property]))
		else left(substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property]))
			,charindex('|',substring([property], charindex('B:',[property])+2,len([property]) - charindex('B:',[property]))) -1
			)
	end
	) as int)>1
;

#14

。。。。难道我写的不对？直接被无视了

#15

引用 14 楼 guguda2008 的回复:

。。。。难道我写的不对？直接被无视了

你的是对的

#16

弄了半天，终于搞定了，最后借鉴了流浪的云的思路，问题解决。感谢各位热情的帮助，结贴给分。