1

I need help for complex data sort from database. Suppose my data stored in table like this:

Description
--------------
JCB Excavator - ECU P/N: 728/35700
Geo Prism 1995 GEO - ABS #16213899 GEO pump
Geo Prism 1995 - GEO ABS #16213897
Geo Prism 1995 - ABS #16213897
Ersatz Airbags, Gurtstrammer und Auto Körper  Teile
this test JCB pipe & JCB pump
Wie man BBA reman erreicht

7 rows are there. I want to write a SQL query in such a way as a result it will sort data based on my input word. Suppose my search term is GEO,JCB two words together separated by comma. The rows that will come first are those where search word found maximum time. So for GEO the word has the maximum time in most of the rows.

Two sorts will be required:

  • first sort data in such way where search term found maximum time.
  • second sort data on the basis maximum occurrence of search term found in each rows.

Suppose GEO the search term found in maximum row.

So all the rows that have GEO keyword will come first and then JCB related data will come.

In GEO related data those rows will come first that have maximum GEO keyword.

So the output will be look like.

Description
--------------
Geo Prism 1995 GEO - ABS #16213899 GEO pump
Geo Prism 1995 - GEO ABS #16213897
Geo Prism 1995 - ABS #16213897
this test JCB pipe & JCB pump
JCB Excavator - ECU P/N: 728/35700
Ersatz Airbags, Gurtstrammer und Auto Körper  Teile
Wie man BBA reman erreicht

Please help me to construct this kind of sql which will work in all SQL Server version.

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Thomas
  • 31,089
  • 118
  • 335
  • 589
  • 2
    Do you *have* to use SQL Server as a text based search engine (like Luence) may be a better solution. SQL Server isn't great at parsing strings (which is what you'll have to do) – Kane Jul 27 '12 at 07:31
  • **ALL** SQL Server versions - wow - how far back?? SQL Server 4.2? 6.0? 6.5? 7.0? 2000? ...... – marc_s Jul 27 '12 at 08:42
  • i am working with sql server 2000....so marc_s can u please help me. thanks – Thomas Jul 27 '12 at 13:36

2 Answers2

1
  1. Write a function to tokenize a string into a table of words. eg: How do I split a string so I can access item x?
  2. Apply this function to the search string and the description table
  3. Join the two results with cross apply; group the results, count and sort.
Community
  • 1
  • 1
podiluska
  • 49,221
  • 7
  • 86
  • 96
0

Here you go

declare @t table(Description varchar(1000))
insert into @t
select 'JCB Excavator - ECU P/N: 728/35700 ' union all
select 'Geo Prism 1995 GEO - ABS #16213899 GEO pump ' union all
select 'Geo Prism 1995 - GEO ABS #16213897 ' union all
select 'Geo Prism 1995 - ABS #16213897 ' union all
select 'Ersatz Airbags, Gurtstrammer und Auto Körper  Teile ' union all
select 'this test JCB pipe & JCB pump ' union all
select 'Wie man BBA reman erreicht'

declare @search_term varchar(100)
set @search_term ='GEO'
select Description from @t
order by len(Description)-len(replace(Description,@search_term,'')) desc

Result

Description
----------------------------------------------------------
Geo Prism 1995 GEO - ABS #16213899 GEO pump 
Geo Prism 1995 - GEO ABS #16213897 
Geo Prism 1995 - ABS #16213897 
Ersatz Airbags, Gurtstrammer und Auto Körper  Teile 
this test JCB pipe & JCB pump 
Wie man BBA reman erreicht
JCB Excavator - ECU P/N: 728/35700
Madhivanan
  • 12,915
  • 1
  • 21
  • 26