-2

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user15950409
  • 11
  • 2
  • _text position_ - as in _character position_ or _word position_? Try to find some sample data that returns different orders for character and word positions. – jarlh May 17 '21 at 12:21
  • yes ! based on the searched text position in string I want to sort a list – user15950409 May 18 '21 at 09:21

2 Answers2

0

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, what if I need to search the strings from multiple columns? currently I am using a command like : na.Name LIKE '%' + @searchedValue + '%' so how should I apply the command with orderBy for above cloumns – user15950409 May 17 '21 at 12:26
  • 1
    @user15950409 . . . This answers the question that you asked here. If you have a different question, ask a *new* question. – Gordon Linoff May 17 '21 at 20:52
0

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.

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
  • Great!, but my requirement is at first position in list I want to display the string which contains the searched value at its first position – user15950409 May 18 '21 at 09:20
  • You are joking right? You need someone to show you how to add a column to a query? What how you tried? How exactly are you stuck on that? – Alan Burstein May 19 '21 at 00:53