Sql Select similar text

You would need to split the string up and search by each word in the string. SQL Server doesn't have a native function to do that, but there are various examples on the web.

This function will take a string and a delimiter, and it will split the string by the delimiter and return a table of the resulting values.

CREATE FUNCTION dbo.SplitVarchar (@stringToSplit varchar(4000), @delimiter CHAR(1))RETURNS  @Result TABLE(Value VARCHAR(50))ASBEGIN    --This CTE will return a table of (INT, INT) that signify the startIndex and stopIndex    --of each string between delimiters.    WITH SplitCTE(startIndex, stopIndex) AS    (      SELECT 1, CHARINDEX(@delimiter, @stringToSplit)   --The bounds of the first word      UNION ALL      SELECT stopIndex + 1, CHARINDEX(@delimiter, @stringToSplit, stopIndex+1)      FROM SplitCTE             --Recursively call SplitCTE, getting each successive value      WHERE stopIndex > 0    )    INSERT INTO @Result    SELECT        SUBSTRING(@stringToSplit,   --String with the delimited data            startIndex,             --startIndex of a particular word in the string            CASE WHEN stopIndex > 0 THEN stopIndex-startIndex   --Length of the word            ELSE 4000 END   --Just in case the delimiter was missing from the string            ) AS stringValue    FROM SplitCTE    RETURNEND;

Once you turn your delimited string into a table, you can JOIN it with the table you wish to search and compare values that way.

DECLARE @TeamName VARCHAR(50)= 'Chelsea FC'SELECT DISTINCT NameFROM TeamINNER JOIN (SELECT Value FROM dbo.SplitVarchar(@TeamName, ' ')) t  ON CHARINDEX(t.Value, Name) > 0

Results:

|    Name ||---------|| Chelsea |

SQL Fiddle example

I based my design on Amit Jethva's Convert Comma Separated String to Table : 4 different approaches

You can use like this way:

declare @s varchar(20) = 'Chelsey FC'select * from Teamwhere name like '%' + @s + '%' or        @s like '%' + name + '%'

This will filter rows if @s contains Name or Name contains @s.

Use Full Text Search

To know more about Full Text Search

http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm

For implementing it

https://msdn.microsoft.com/en-IN/library/ms142571.aspx