Click here to Skip to main content
14,303,949 members
Rate this:
Please Sign up or sign in to vote.
See more:
i want to search character OR word in a table.EG i want to do the following steps...

1)load all records

2)Loop all records and check number of matching characters

3)Return those records have more matched characters.


how i can do this through Stored procedure?


Regards
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

One way is that:
- you define a cursor for your select using DECLARE CURSOR[^]
- loop through the records using FETCH NEXT[^]
- in the loop use CHARINDEX[^] to find the number of occurences

If you want to return several rows, you can create a temporary table[^] and add rows to return into that table and in the end select from that table in order to return the results.

However, if you're only interested in rows having most of certain character, you don't need a procedure for that. You can count the number of occurences by comparing the original length of a string to a length of a string where desired characters are removed. Consider the following:
select SomeColumn, 
       len(SomeColumn) - len(replace(SomeColumn, 'SomeCharacter', '')) as numberofoccurences 
from sometable
   
Comments
Member 7932838 5-Sep-11 5:56am
   
Thanks alot for your Reply....For now i am using this Query
select COMPANY,
len(COMPANY) - len(replace(COMPANY, 'NGSP', '')) as numberofoccurences
from companies order by numberofoccurences DESC
This shows me COMPANY and numberofoccurences as
KLINGSPOR PTE LTD 4
This is fine but i want to get only "KLINGSPOR" NOT "KLINGSPOR PTE LTD"
how it could be?
Wendelius 5-Sep-11 6:18am
   
Do you mean you want only the text till the first space? If that's the case you can use something like:
select substring(company, 1, charindex(' ', company)), ...
Member 7932838 5-Sep-11 6:35am
   
I do not mean to use upto First space.....that could be the second or third spaces....
Ok help me if i want to use CURSOR....i need two loop(inner loops) as if i want to search "GO" in "KLINGSPOR PTE LTD", i have to compare G with each record and then O with that record again...how i could do this....actually i want to do like google search to give suggestions from my db..."Did you mean this or that" etc

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100