select * from tb where charindex(','+a+',',','+'1'+',')>0
select * from 表 where patindex('%,1,%',','+A',')>0
select * from tb where charindex(',1,',','+a+',') > 0
select * from tb where charindex(',1,',','+A+',')>0
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-09-01 10:11:29 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] varchar(9)) insert [tb] select '100,1,103' union all select '100,3,1' union all select '12,5,8' --------------开始查询-------------------------- select * from tb where charindex(',1,',','+A+',')>0 ----------------结果---------------------------- /* A --------- 100,1,103 100,3,1(2 行受影响) */
select * from tb where charindex(','+a+',',','+'1'+',')>0
/*--------------------------------- -- Author : htl258(Tony) -- Date : 2009-09-01 10:12:23 -- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/ --> 生成测试数据表:tbIf not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([A] nvarchar(9)) Insert [tb] Select N'100,1,103' union all Select N'100,3,1' union all Select N'12,5,8' Go --Select * from [tb]-->SQL查询如下:select * from tb where charindex(',1,',','+A+',')>0/* 100,1,103 100,3,1 */
弄反了select * from tb where charindex(',1,',','+列+',')>0
declare @t table (f1 varchar(50))insert @t select '100,1,103' union all select '100,3,1' union all select '12,5,8' select * from @t where charindex(',1,',','+f1+',')<>0/* (所影响的行数为 3 行)f1 -------------------------------------------------- 100,1,103 100,3,1(所影响的行数为 2 行)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-01 10:11:29
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(9))
insert [tb]
select '100,1,103' union all
select '100,3,1' union all
select '12,5,8'
--------------开始查询--------------------------
select * from tb where charindex(',1,',','+A+',')>0
----------------结果----------------------------
/* A
---------
100,1,103
100,3,1(2 行受影响)
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-01 10:12:23
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)---------------------------------*/
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([A] nvarchar(9))
Insert [tb]
Select N'100,1,103' union all
Select N'100,3,1' union all
Select N'12,5,8'
Go
--Select * from [tb]-->SQL查询如下:select * from tb where charindex(',1,',','+A+',')>0/*
100,1,103
100,3,1
*/
declare @t table (f1 varchar(50))insert @t
select '100,1,103' union all
select '100,3,1' union all
select '12,5,8'
select *
from @t
where charindex(',1,',','+f1+',')<>0/*
(所影响的行数为 3 行)f1
--------------------------------------------------
100,1,103
100,3,1(所影响的行数为 2 行)*/