CODEWITHSUNDEEP
t-sqlsql-server-2008full-text-search
John Kalberer
John Kalberer

Reputation: 5790

SQL basic full-text search

I have not worked much with TSQL or the full-text search feature of SQL Server so bear with me.

I have a table nvarchar column (Col) like this:

       Col  ... more columns
Row 1: '1'
Row 2: '1|2'
Row 3: '2|40'

I want to do a search to match similar users. So if I have a user that has a Col value of '1' I would expect the search to return the first two rows. If I had a user with a Col value of '1|2' I would expect to get Row 2 returned first and then Row 1. If I try to match users with a Col value of '4' I wouldn't get any results. I thought of doing a 'contains' by splitting the value I am using to query but it wouldn't work since '2|40' contains 4...

I looked up the documentation on using the 'FREETEXT' keyword but I don't think that would work for me since I essentially need to break up the Col values into words using the '|' as a break.

Thanks, John

Upvotes: 0

Views: 168

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

You should not store values like '1|2' in a field to store 2 values. If you have a maximum of 2 values, you should use 2 fields to store them. If you can have 0-many values, you should store them in a new table with a foreign key pointing to the primary key of your table..

If you only have max 2 values in your table. You can find your data like this:

DECLARE @s VARCHAR(3) = '1'

SELECT * 
FROM <table> 
WHERE @s IN( 
    PARSENAME(REPLACE(col, '|', '.'), 1),  
    PARSENAME(REPLACE(col, '|', '.'), 2) 
    --,PARSENAME(REPLACE(col, '|', '.'), 3) -- if col can contain 3 
    --,PARSENAME(REPLACE(col, '|', '.'), 4) -- or 4 values this can be used
  )

Parsename can handle max 4 values. If 'col' can contain more than 4 values use this

DECLARE @s VARCHAR(3) = '1'

SELECT * 
FROM <table> 
WHERE '|' + col + '|' like '%|' + @s + '|%'

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

Need to mix this in with a case for when there is no | but this returns the left and right hand sides 

      select left('2|10', CHARINDEX('|', '2|10') - 1)
      select right('2|10', CHARINDEX('|', '2|10'))

Upvotes: 0

Related Questions