---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-03-06 07:50:04 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([A] nvarchar(22)) insert [huang] select '1,2,5,16,18' union all select '2,4,5,7,12' union all select '9,3,4,5' union all select '3,8,9' --------------生成数据--------------------------select * from [huang] WHERE CHARINDEX(',3',A)>0 ----------------结果---------------------------- /* A ---------------------- 9,3,4,5*/
WITH a1 (a) AS ( SELECT '1,2,5,16,18' UNION ALL SELECT '2,4,5,7,12' UNION ALL SELECT '9,3,4,5' UNION ALL SELECT '3,8,9' ) SELECT * FROM a1 WHERE CHARINDEX(',3,',','+a+',')>0
这意思是不是用逗号做分割 然后一个数字一列?select id = identity(int,1,1) into #B from 表名,表名(如果数据少的话 用系统表也行)select substring(A, B.id, charindex(';', A + ';', B.id) - B.id) --B表为序号 from 表名,#B B
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-03-06 07:50:04
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([A] nvarchar(22))
insert [huang]
select '1,2,5,16,18' union all
select '2,4,5,7,12' union all
select '9,3,4,5' union all
select '3,8,9'
--------------生成数据--------------------------select *
from [huang]
WHERE CHARINDEX(',3',A)>0
----------------结果----------------------------
/*
A
----------------------
9,3,4,5*/
where charindex(',3,',','+ltrim(A)+',')>0
CHARINDEX(substring,字符串,[startposition])
在字符串中 查找substring第一次出现的位置 如果没有找到则返回0,startposition 可选参数,从第几个位置开始查找
WITH a1 (a) AS
(
SELECT '1,2,5,16,18' UNION ALL
SELECT '2,4,5,7,12' UNION ALL
SELECT '9,3,4,5' UNION ALL
SELECT '3,8,9'
)
SELECT *
FROM a1
WHERE CHARINDEX(',3,',','+a+',')>0
into #B
from 表名,表名(如果数据少的话 用系统表也行)select substring(A, B.id, charindex(';', A + ';', B.id) - B.id) --B表为序号
from 表名,#B B