How to search character by character in a table through stored procedure

Question

0.00/5 (No votes)

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 4-Sep-11 21:10pm

Member 7932838

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2011-09-04T21:30:00

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:

SQL

select SomeColumn, 
       len(SomeColumn) - len(replace(SomeColumn, 'SomeCharacter', '')) as numberofoccurences 
from sometable

Posted 4-Sep-11 21:30pm

Wendelius

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