June 13, 2008 at 9:05 am
Hi there,
How can I do a search on the % sign in a like clause on sql2k5 sp2 ?? I have try to double it like %% or char it but without any success.
thanx
June 13, 2008 at 9:10 am
It should work just like it has in prior versions unless I've missed something. Can you give us an example of some T-SQL that you've ran where it has not worked?
SELECT *
FROM MyTable
WHERE MyValue LIKE 'Value%'
June 13, 2008 at 9:15 am
Well, if it's was that it will be too easy, what I really want is
SELECT *
FROM MyTable
WHERE MyValue LIKE '%'
but % is a wildcard so it return everything and that's not what I want. I only want those contening the % sign...
June 13, 2008 at 9:18 am
Ah, sorry. I misunderstood you question. Why does it have to be in a LIKE clause? Why not CHARINDEX?
declare @table table (col1 varchar(100))
insert into @table
select 'this value % has a percent' union all
select 'this one does not'
select *
from @table
where charindex('%',col1) > 0
June 13, 2008 at 9:18 am
to look for the literal %, enclose it between square brackets.
Something like:
create table #strings(str_all varchar(20))
insert #strings
select 'cow 1' union all
select 'cow 2' union all
select 'cow 3' union all
select 'cow%1' union all
select 'cow%2'
select * from #strings
where str_all like 'cow[%]%' --<--first one is the literal, second one is the wildcard
drop table #strings
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 13, 2008 at 9:23 am
In addition to specifying the wildcard character as a literal there's also the ESCAPE clause that can be used.
SELECT * FROM myTable WHERE column1 LIKE '%/%%' ESCAPE '/'
June 13, 2008 at 9:23 am
Thanks Matt..
I should know that one!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply