CODEWITHSUNDEEP
sqlsql-serverstored-proceduresfull-text-searchwildcard
Jhorra
Jhorra

Reputation: 6321

Full Text Search Using Multiple Partial Words

I have a sql server database that has medical descriptions in it. I've created a full text index on it, but I'm still figuring out how this works.

The easiest example to give is if there is a description of Hypertensive heart disease

Now they would like to be able to type hyp hea as a search term and have it return that.

So from what I've read it seems like my query needs to be something like

DECLARE @Term VARCHAR(100)
SET @Term = 'NEAR(''Hyper*'',''hea*'')'
SELECT * FROM Icd10Codes WHERE CONTAINS(Description, @Term)

If I take the wild card out for Hypertensive and heart, and type out the full words it works, but adding the wild card in returns nothing.

If it makes any difference I'm using Sql Server 2017

Upvotes: 2

Views: 2700

Answers (4)

johndpope
johndpope

Reputation: 5249

For me - this had more mileage. create a calculated row with fields as full text search. fullname / company / lastname all searchable.

    ALTER TABLE profiles ADD COLUMN fts tsvector generated always as (to_tsvector('english', coalesce(profiles.company, '') || ' ' || coalesce(profiles.broker, '') || ' ' || coalesce(profiles.firstname, '') ||  ' ' || coalesce(profiles.lastname, '') || ' ' )) stored;



      let { data, error } = await supabase.from('profiles')
      .select()
      .textSearch('fts',str)

Upvotes: 0

Jhorra
Jhorra

Reputation: 6321

So it was a weird syntax issue that didn't cause an error, but stopped the search from working.

I changed it to

SELECT * FROM Icd10Codes where CONTAINS(description, '"hyper*" NEAR "hea*"')

The key here being I needed double quotes " and not to single quotes. I assumed it was two single quotes, the first to escape the second, but it was actually double quotes. The above query returns the results exactly as expected.

Upvotes: 3

Bronathan
Bronathan

Reputation: 26

You could try a like statement. You can find a thorough explanation here.

Like so:

SELECT * FROM Icd10Codes WHERE Icd10Codes LIKE '%hyp hea%';

And then instead of putting the String in there just use a variable.

If you need to search for separated partial words, as in an array of search terms, it gets a bit tricky, since you need to dynamically build the SQL statement.

MSSQL provides a few features for full text search. You can find those here. One of them is the CONTAINS keyword:

SELECT column FROM table WHERE CONTAINS (column , 'string1 string2 string3');

Upvotes: 0

Nikhil
Nikhil

Reputation: 3950

this will work:

SELECT * FROM Icd10Codes where SOUNDEX(description)=soundex('Hyp');

SELECT * FROM Icd10Codes where DIFFERENCE(description,'hyp hea')>=2;

Upvotes: 0

Related Questions