March 16, 2011 at 3:45 pm
Good afternoon all,
how can I search a column for a specific word without using the Contains keyword (the dba won't turn on full-text-indexing).
Example:
create table #test(col varchar(50))
insert into #test (col)
select 'a strategic move' union select 'random test' union select 'rate is 5' union select 'these are the rates'
drop table #test
Should only return 'rate is 5'. So I essentially want to use the "contains" function without using it.
TIA
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 16, 2011 at 3:50 pm
There are 3 ways with which u can do that
This may use indexes, if present.
Select * from #test where col LIKE '%rate is 5%'
THis wont use indexes
Select * from #test where charindex('rate is 5',col) > 0
And the last one is using PATINDEX, which i leave to u to figure out 🙂
March 16, 2011 at 3:58 pm
Thanks for the quick reply CC, but I don't want to search for the phrase 'rate is 5', I only want results that have the word 'rate'.
Let me add this to the example:
create table #test(col varchar(50))
insert into #test (col)
select 'a strategic move' union select 'random test' union select 'rate is 5' union select 'these are the rates' union select '0 is the rate'
select * from #test where charindex('rate', col) > 0
drop table #test
I need to change the select to return 'rate is 5' and '0 is the rate'
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 16, 2011 at 6:09 pm
How about this:
select * from #test where right(SUBSTRING((col+' '),(CHARINDEX('rate',col)),5),1) = ' '
March 17, 2011 at 7:38 am
I must admit I am impressed with the cleverness of your solution...but I have another failed test clause. Insert the word 'substrate' to the #test and your query will select it. So close, but I see why this simple request is so difficult.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 17, 2011 at 7:56 am
WHERE CHARINDEX(' rate ', ' '+col+' ') > 0
or
WHERE ' '+col+' ' LIKE '% rate %'
Far away is close at hand in the images of elsewhere.
Anon.
March 17, 2011 at 8:43 am
David Burrows (3/17/2011)
WHERE CHARINDEX(' rate ', ' '+col+' ') > 0or
WHERE ' '+col+' ' LIKE '% rate %'
Perfect, David. Works great, but why does it work? Does SS just ignore the spaces when comparing '% rate %' to 'rate'?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 17, 2011 at 9:20 am
toddasd (3/17/2011)
Does SS just ignore the spaces when comparing '% rate %' to 'rate'?
No the spaces are important. You wanted to find particular words ie rate and words are separated by spaces. So by searching for <space>rate<space> we are looking for the exact word rate and not words like irate and rates. The addtional spaces put at the beginning and end of the searched text is there to make sure the word is found if at the beginning or end without leading/trailing space(s).
Also the % is a wildcard symbols when used in LIKE or PATINDEX and means any text, so LIKE is looking for
<anything><space>rate<space><anything>, eg in the text 'the rate is 5' it would ignore 'the' and 'is 5'.
Far away is close at hand in the images of elsewhere.
Anon.
March 17, 2011 at 10:14 am
Ok, now I see where my question was silly. It's not comparing '% rate %' to 'rate' because you added the spaces before and after and then did the wildcard compare. I need to stop asking questions before coffee.
But now the problem is...
... and words are separated by spaces.
Not necessarily true because of punctuation. So new test data 'The rate: $6500' now fails. My intent from the beginning was to check the characters before and after 'rate' to see if they are also letters, but I didn't know how to write that. Now I can combine ColdCoffee's work and your work, David, to get this:
create table #ASCII_Letter_Values (Value int)
declare @i int
set @i = 65
while @i < 91
begin
insert into #ASCII_Letter_Values Values (@i), (@i+32) --lower case and capitals
set @i = @i + 1
end
create table #test(col varchar(50))
--test data
insert into #test (col)
select 'a strategic move' union select 'random test' union select 'rate is 5' union select 'these are the rates' union select '0 is the rate' union select 'substrate'
union select 'Rate Sheet' union select 'Rates 2002' union select 'The rate: $6500'
--only want to return where the word 'rate' is in the description
--output should only be 'rate is 5','0 is the rate','Rate Sheet', and 'The rate: $6500'
select col from #test where
isnull(ascii(substring(col,charindex('rate', col) + 4,1)),0) not in (select Value from #ASCII_Letter_Values)
and
isnull(ascii(substring(col,charindex('rate', col) - 1,1)),0) not in (select Value from #ASCII_Letter_Values)
--clean up
drop table #ASCII_Letter_Values
drop table #test
Which is kind of convoluted, but works so far. Does anyone see any problems or improvements?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 17, 2011 at 10:42 am
Try this
LIKE '%rate[!"'',.:;? ]%'
You can add additional chars between [] but check LIKE and PATINDEX on BOL (Books Online) for special chars and how to ESCAPE them.
Far away is close at hand in the images of elsewhere.
Anon.
March 17, 2011 at 11:48 am
Yep, that may be a little shorter than my version 😉
select * from #test where ' '+col+' ' like '%[!",.'':;? ]rate[!",.'':;? ]%'
Thanks David.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
March 17, 2011 at 12:23 pm
David Burrows (3/17/2011)
Try this
LIKE '%rate[!"'',.:;? ]%'You can add additional chars between [] but check LIKE and PATINDEX on BOL (Books Online) for special chars and how to ESCAPE them.
You are probably better off using the ^ operator instead of listing all potential non-alphabetic characters. The ^ operator gives you all characters not specified by the expression in the brackets.
LIKE '%[^A-Za-z]rate[^A-Za-z]%'
If your collation is Case Insensitive, you don't need both ranges.
LIKE '%[^A-Z]rate[^A-Z]%'
Drew
Edited to add in % wildcards.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 17, 2011 at 1:09 pm
Yep, this is my new favorite. Thanks Drew.
If this improvement keeps up every post, pretty soon this will be a built-in SS function! Oh wait...:-)
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply