September 6, 2011 at 2:28 am
i want to search the no of characters in a field and to show the record having more characters matched.
how i can do these steps in stored proceedure
1)load all record
2)loop all records and check number of matching charactes
3)return the records having more matched
September 6, 2011 at 2:39 am
Using looping / cursoring in SQLServer , is very much frowned upon as its slow.
*Sometimes* its the only way to do things but not here.
Is it a single character that you need to count ? You could use replace to replace the character with a blank ie '' and count the string lengths before and after.
If multiple characters , use a tally table to split the string ala http://www.sqlservercentral.com/articles/T-SQL/62867/ and simply use the count aggregate.
HTH
September 6, 2011 at 2:48 am
engrshafiq4 (9/6/2011)
i want to search the no of characters in a field and to show the record having more characters matched.how i can do these steps in stored proceedure
1)load all record
2)loop all records and check number of matching charactes
3)return the records having more matched
This requirement is not very clear. If you are just looking for columns wider than a certain number, use something like this:
select *
from table
where len(columnName) > 20
If that is not what you want, please provide some sample data, and example searches to clarify the requirement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2011 at 2:49 am
Ok let me check your code.
Actually my case is....
i have a table have thousands of data about company and people do search for any company.
i want to provide related/suggested search to people as if a user type uncorrect/misspell company name so i will show all those names having more matched characters he/she entered...just like google search do...by "DId you mean" etc etc
September 6, 2011 at 2:51 am
you could try and use the SOUNDEX function in T-sql and do this without a loop/cursors
http://msdn.microsoft.com/en-us/library/ms187384.aspx"> http://msdn.microsoft.com/en-us/library/ms187384.aspx
September 6, 2011 at 2:53 am
Actually i want to provide options to users regarding their search .....
Eg if some one write "str" i want to show him "star" OR "sstr" mean nearly matching my db field world
just like google search do
September 6, 2011 at 2:57 am
to get the anywhere near the sort of functionality that google has, then you will probably need to do some coding in an application langauge such as c# or Python or whatever.
here is a good article on how to have a 'google' like search.
http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/"> http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/
September 6, 2011 at 3:01 am
And here is a soundex example.
;with tempData as
(select 'star' Company union select 'stir' union select 'My Fab Company')
select company from tempData
where soundex(company) like soundex('str')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2011 at 3:05 am
Thanks for your reply
But i am facing project Deadline and do not have much time to implement these coding...
Can you please give me a stored procedure which do the following steps
1)load all records
2)set a threshold percent value
3)loop all record and check number of matching characters in sequence
4)add threshold selected records in a temporary table or list
return list/table
September 6, 2011 at 3:08 am
engrshafiq4 (9/6/2011)
Thanks for your replyBut i am facing project Deadline and do not have much time to implement these coding...
Can you please give me a stored procedure which do the following steps
1)load all records
2)set a threshold percent value
3)loop all record and check number of matching characters in sequence
4)add threshold selected records in a temporary table or list
return list/table
No. We're here to help, not to do your work for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2011 at 3:15 am
@SSCrazy
ok my questions are
1) how i will load table data to tempData(in your example)?
2) what are star ,Stir,My Fab Company etc in your example....eg (select 'star' Company union select 'stir' union select 'My Fab Company')
i want to use this example as this is helpful to me
September 6, 2011 at 3:33 am
engrshafiq4 (9/6/2011)
@SSCrazyok my questions are
1) how i will load table data to tempData(in your example)?
2) what are star ,Stir,My Fab Company etc in your example....eg (select 'star' Company union select 'stir' union select 'My Fab Company')
i want to use this example as this is helpful to me
1) You won't. I was using a CTE as a way of generating sample data. You should use your source table. I used a CTE as a way of creating a query which will run without a database - just load into SSMS and execute.
2) As above - just sample data - a combination of yours and mine.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2011 at 4:41 am
@SSCrazy
Thanks alot ...worked for me...
September 7, 2011 at 2:45 am
Hi,
this is working fine for me.
select TOP 20 COMPANY,
len(COMPANY) - len(replace(COMPANY, 'piz', ''))
as numberofoccurences
from vwSearchResult order by numberofoccurences DESC
Can i do it for more fields?i used only COMPANY field here.is it possible and how?
September 7, 2011 at 3:49 am
engrshafiq4 (9/7/2011)
Hi,this is working fine for me.
select TOP 20 COMPANY,
len(COMPANY) - len(replace(COMPANY, 'piz', ''))
as numberofoccurences
from vwSearchResult order by numberofoccurences DESC
Can i do it for more fields?i used only COMPANY field here.is it possible and how?
Hmm, not 100% sure what you mean. Maybe something like this:
select 'Company' FieldName,
len(COMPANY) - len(replace(COMPANY, 'piz', '')) as numberofoccurences
from vwSearchResult
union
select 'F1',
len(F1) - len(replace(F1, 'piz', ''))
from vwSearchResult
order by numberofoccurences desc
Replace F1 with the name of the field you wish to check.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply