July 20, 2009 at 12:41 pm
Can someone tell me how to correctly use 'c#' in the following where statement ?
Thanks
SELECT DISTINCT Email
FROM (SELECT Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
where (contains (originalresume, '"Oracle"
and "c#" ------------------------- THIS WILL NOT WORK !------------
and "unix"
and "sql"'))
) p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
where Email like '%@%'
order by email
July 20, 2009 at 12:55 pm
Have you tried assigning it to a variable? Contains can take variables in the argument.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 20, 2009 at 1:30 pm
I tried the following code without errors, but it takes forever 🙁
Thanks
declare @skills varchar (100);
set @skills='"c#" and "livelink"';
SELECT DISTINCT Email
FROM (SELECT Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
where (contains (originalresume, '@skills'))
) p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
where Email like '%@%'
order by email
July 21, 2009 at 9:12 am
Have you tried breaking down the steps to see what's slowing it down? Or checked the execution plan for that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2009 at 9:17 am
are you really using double quotes for your varchar constants? You should be using single quotes.
The probability of survival is inversely proportional to the angle of arrival.
July 22, 2009 at 9:10 am
ifila (7/20/2009)
I tried the following code without errors, but it takes forever 🙁Thanks
declare @skills varchar (100);
set @skills='"c#" and "livelink"';
SELECT DISTINCT Email
FROM (SELECT Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
where (contains (originalresume, '@skills'))
) p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
where Email like '%@%'
order by email
This is a bit foreign ground for me....I haven't use FULL TEXT searching, and I know the syntax can be a bit different but....
...I would be surprised if where (contains (originalresume, '@skills')) works, as the variable is inside quotes i.e. it will be treated as the text @skills.
Or is the syntax really so different?
July 22, 2009 at 9:42 am
# is a wildcard character for 'any number'.
you need to escape it in your search. If I read BOL correctly you need to search for 'c\#' where the backslash is the literal escape character to force a search for the pound sign.
And yes also to the previous 2 posts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply