Using SQL Server how to sort a list of strings based on searched value(Searched text) position in string

Question

I have to sort a list based on the searched text position in the string i.e. if the searched value appears first in the string then its position should be first in the list, if it appears second in the string then it should be second but all the strings with first word as searched value should appear first in the list.

For example, input strings are:

• Chris is a good person
• Every person should be like chris
• Not every person is chris
• person like chris are good

The searched value is "person".

Output should be :

• person like chris are good
• Every person should be like chris
• Not every person is chris
• Chris is a good person

Answer 1

You can grab a copy of NGrams8k and analyze strings a number of ways. This will sort by the position as well as returning it.

Results:

Txt                                  Position
------------------------------------ ----------
person like chris are good           1
Every person should be like chris    7
Not every person is chris            11
Chris is a good person               17

If you change the search text to "Chris" you get:

Txt                                 Position
----------------------------------- --------------------
Chris is a good person              1
person like chris are good          13
Not every person is chris           21
Every person should be like chris   29

Something else you could do with NGrams8K is sort by the number of occurrences of your search string.

--==== Searching by number of occurances
INSERT @t VALUES('Chris said "person", what a person'),('Person person and more persons');
SET @search = 'Person';

SELECT t.Txt, Occurances = COUNT(*)
FROM        @t AS t
CROSS APPLY samd.ngrams8k(t.txt,LEN(@search)) AS ng
WHERE       ng.Token = @search
GROUP BY    t.Txt
ORDER BY -COUNT(*);

Returns:

Txt                                  Occurances
------------------------------------ -----------
Person person and more persons       3
Chris said "person", what a person   2
Every person should be like chris    1
Not every person is chris            1
person like chris are good           1
Chris is a good person               1

Cheers.

Answer 2

Use charindex():

order by charindex('person', column)

This assumes that the column has the string.

If it does not have the string, you can use a case expression, or convert to NULL and then to a big value:

order by coalesce(nullif(charindex('person', column), 0), 999999)