DECLARE @TB TABLE(id INT, code VARCHAR(10))
INSERT @TB
SELECT 1, 'a' UNION ALL
SELECT 2, 'ax' UNION ALL
SELECT 3, 'a1' UNION ALL
SELECT 4, 'a1x' UNION ALL
SELECT 5, 'a2' UNION ALL
SELECT 6, 'a8' UNION ALL
SELECT 7, 'a1' UNION ALL
SELECT 8, 'a'SELECT * FROM @TB WHERE CHARINDEX(CODE,'a1x')>0
/*
id code
----------- ----------
1 a
3 a1
4 a1x
7 a1
8 a
*/
INSERT @TB
SELECT 1, 'a' UNION ALL
SELECT 2, 'ax' UNION ALL
SELECT 3, 'a1' UNION ALL
SELECT 4, 'a1x' UNION ALL
SELECT 5, 'a2' UNION ALL
SELECT 6, 'a8' UNION ALL
SELECT 7, 'a1' UNION ALL
SELECT 8, 'a'SELECT * FROM @TB WHERE CHARINDEX(CODE,'a1x')>0
/*
id code
----------- ----------
1 a
3 a1
4 a1x
7 a1
8 a
*/
insert @t select 1,'a'
insert @t select 2,'ax'
insert @t select 3,'a1'
insert @t select 4,'a1x'
insert @t select 5,'a2'
insert @t select 6,'a8'
insert @t select 7,'a1'
insert @t select 8,'a'select * from @t where charindex(code,'a1x')>0id code
----------- ----------
1 a
3 a1
4 a1x
7 a1
8 a(5 行受影响)
insert @t select 1,'a'
insert @t select 2,'ax'
insert @t select 3,'a1'
insert @t select 4,'a1x'
insert @t select 5,'a2'
insert @t select 6,'a8'
insert @t select 7,'a1'
insert @t select 8,'a'select * from @t where 'a1x' like '%'+code+'%'id code
----------- ----------
1 a
3 a1
4 a1x
7 a1
8 a(5 行受影响)
怎么不行?
用 rtrim(code) ,然后随便用上面哪个语句即可
用這個吧,感覺比charindex好像更貼切些,呵呵
select * from @t where charindex(ltrim(rtrim(code)),'a1x')>0