Sql Select similar text Sql Select similar text sql sql

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


|    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.