Get words from sentence - SQL

Question

Hi

Suppose I have a description column that contains

Column Description
------------------
I live in USA
I work as engineer 

I have an other table containing the list of countries, since USA (country name) is mentioned in first row, I need that row.

In second case there is no country name so I don't need that column.

Can you please clarify

Answer 1

This is a really bad idea, to join on arbitrary text like this. It will be very slow and may not even work.. give it a shot:

select t1.description, c.*
from myTable t1
left join countries c on t1.description like CONCAT('%',c.countryCode,'%')

Answer 2

You may want to try something like the following:

SELECT   cd.* 
FROM     column_description cd
JOIN     countries c ON (INSTR(cd.description, c.country_name) > 1);

If you are using SQL Server, you should be able to use the CHARINDEX() function instead of INSTR(), which is available for MySQL and Oracle. You can also use LIKE as other answers have suggested.

Test case:

CREATE TABLE column_description (description varchar(100));
CREATE TABLE countries (country_name varchar(100));

INSERT INTO column_description VALUES ('I live in USA');
INSERT INTO column_description VALUES ('I work as engineer');

INSERT INTO countries VALUES ('USA');

Result:

+---------------+
| description   |
+---------------+
| I live in USA |
+---------------+
1 row in set (0.01 sec)

Answer 3

Hi,

Its not entierly clear from your post but I think you are asking to return all the rows in the table that contain the descriptions which contain a certain country name? If thats the case you can just use the sql LIKE operator like the following.

select
 column_description
from
 description_table
where
 column_description like %(select distinct country_name from country)%

If not I think your only other choice is Dans post.

Enjoy !

Answer 4

All the suggestions so far seem to match partial words e.g. 'I AM USAIN BOLT' would match the country 'USA'. The question implies that matching should be done on whole words.

If the text was consisted entirely of alphanumeric characters and each word was separated by a space character, you could use something like this

Descriptions AS D1
LEFT OUTER JOIN Countries AS C1
   ON ' ' + D1.description + ' '
      LIKE '%' + ' ' + country_name + ' ' + '%'

However, 'sentence' implies punctuation e.g. the above would fail to match 'I work in USA, Goa and Iran.' You need to delimit words before you can start matching them. Happily, there are already solutions to this problem e.g. full text search in SQL Server and the like. Why reinvent the wheel?

Another problem is that a single country can go by many names e.g. my country can legitimately be referred to as 'Britain', 'UK', 'GB' (according to my stackoverflow profile), 'England' (if you ask my kids) and 'The United Kingdom of Great Britain and Northern Ireland' (the latter is what is says on my passport and no it won't fit in your NVARCHAR(50) column ;) to name but a few.