-2

I have a Text, 

'Me and you against the world' // false
'Can i have an email address'  // true
'This is an'  // true
'an' //true

I want to check whether the word an is inside my String.

How do I check if a text contains a specific word in SQL? I can't add a full-text catalog. Otherwies i could

SELECT * FROM TABLE WHERE CONTAINS(Text, 'an')
fubo
  • 44,811
  • 17
  • 103
  • 137

4 Answers4

7

Here's one approach.

DECLARE @table_name table (
   column_name varchar(50)
);

INSERT INTO @table_name (column_name)
  VALUES ('Me and you against the world')
       , ('Can i have an email address')
       , ('This is an')
;

SELECT column_name
FROM   @table_name
WHERE  ' ' + column_name + ' ' LIKE '% an %'
;
gvee
  • 16,732
  • 35
  • 50
3

There are some way to do this, seem you want find a word and not a part of a word, so you can do in easy way with like operator

You can have 3 cases to found a word

  1. 'space'WORD
  2. WORD'space'
  3. 'space'WORD'space'

SELECT * FROM TABLE WHERE Field like ' an' OR Field like 'an ' OR Field like ' an '

Hope it helps

Luka Milani
  • 1,541
  • 14
  • 21
  • Just missing your wildcards in the LIKE comparisons - other than that - nice solution! – gvee Apr 01 '15 at 10:53
  • @gvee if there would be %-wildcards in Luka Milani's answer, it would not match if `an` is the only word - because there are no space in the text – fubo Apr 01 '15 at 11:08
  • @fubo the `%` wildcard matches "Any string of *zero or more* characters." (https://msdn.microsoft.com/en-us/library/ms179859.aspx) – gvee Apr 01 '15 at 11:09
  • the field "an" (from the example) is not matched. Need to add **" OR Field='an' "** to the list :) edit - it's actually more complicated ... like this sentence _"an, or not"_ – Fenix Aoras Sep 18 '17 at 14:28
  • You're missing the wildcards. – pim Sep 06 '18 at 16:34
2

It is perfectly done in MS SQL Server by the CHARINDEX function (it is internal to MS SQL):

if CHARINDEX('an ',@mainString) > 0
begin
    --do something
end

The solution was showed before in another post.

Community
  • 1
  • 1
Javier Salazar
  • 69
  • 1
  • 9
  • Wrong answer - check expected results. – Arvo Apr 01 '15 at 10:41
  • @Arvo why wrong answer? He wanted a function to check if a character/string is in another string, CHARINDEX don't do it? If finding it in the string will give back a number of its position, so its contained.. – Javier Salazar Apr 01 '15 at 10:53
  • @JavierSalazar "This example contains the word **an**d not the search term alone" – gvee Apr 01 '15 at 10:55
  • 1
    @Javier Salazar - he actually wanted to search for entire words, not substring; expected result for search for 'an' was "'Me and you against the world' // false" - your solution needs to add spaces somewhere. – Arvo Apr 01 '15 at 10:56
  • @gvee there's such a huge space for discussion there, thats why first thing I did was adding the link. Anyway, it looks faster to me, since using Like will need of doing all upper and down case options (it's case sensitive) and [CHARINDEX](https://msdn.microsoft.com/en-us/library/ms186323.aspx) is not case sensitive if not using Collate. – Javier Salazar Apr 01 '15 at 10:59
  • @arvo I get your point and yes, it's just to add a space in the searched string. Thanks! – Javier Salazar Apr 01 '15 at 11:01
  • @JavierSalazar `LIKE` is only case sensitive if the collation used is case sensitive. – gvee Apr 01 '15 at 11:02
  • @JavierSalazar "what sentences ending with **an**" – gvee Apr 01 '15 at 11:03
  • @gvee your answer is not correct either actually... if you put spaces in both sides It'll not find the word at the beginning or the end. Nor when having 'An'. Isnt it? Have you tried? – Javier Salazar Apr 01 '15 at 11:05
  • 2
    @JavierSalazar you've missed the other subtlety in my code (a space is appended to the beginning and end of the original value for comparison purposes). Run the whole thing, you'll see. – gvee Apr 01 '15 at 11:07
0

The three cases you'll encounter as Luka mentions:

  1. Space before word
  2. Space after word
  3. Space before and after word

To accomplish this, you'll write a query like the following which searches for the whole word, and pads the expression to search with a leading and trailing space to capture words at the start/end of the expression:

Note: I've used a contrived example to make this portable and demonstrable.

select
  t.txt
from (
  select
    'this is an awesome test of awesomeness man' as txt
) t
where
  charindex(' an ', ' ' + t.txt + ' ') > 0;
pim
  • 12,019
  • 6
  • 66
  • 69