34,588
社区成员
发帖
与我相关
我的任务
分享
select * from 表A
where charindex('|1|','|'+cityid)>0
CREATE TABLE #TB
(id INT,
name VARCHAR(10),
cityid VARCHAR(1000))
INSERT INTO #tb
SELECT 1,'A1','1|2|3|4|5|6|7|8|9|10|11|'
UNION ALL
SELECT 1,'A2','2|3|5|6|7|8|9|10|11|12|'
WITH cte AS (
SELECT id,NAME,cityid=cast(LEFT(cityid,CHARINDEX('|',cityid)-1) AS VARCHAR),
path=cast(STUFF(cityid,1,CHARINDEX('|',cityid),'') AS VARCHAR) FROM #tb
UNION ALL
SELECT id,NAME,cityid=cast(LEFT(path,CHARINDEX('|',path)-1) AS VARCHAR) ,
path=cast(STUFF(path,1,CHARINDEX('|',path),'') AS VARCHAR) FROM cte
WHERE PATH!='')
SELECT id,NAME,cityid FROM CTE WHERE cityid='1'
if OBJECT_ID('t') is not null drop table t
create table t
(
id int,
name varchar(20),
cityid varchar(500)
)
insert into t
select 1,'A1','1|2|3|4|5|6|7|8|9|10|11|' union
select 1,'A2','2|3|5|6|7|8|9|10|11|12|'
select t.name,('|'+t.cityid)
from
t
where
'|'+t.cityid like '%'+'|'+'1|%'