April 18, 2008 at 8:22 am
I'm trying to convert the following query from Oracle to SQL Server.
SELECT COUNT(1) NO_OF_ROWS FROM ADOPTION_PERSON WHERE (
CONTAINS(ADOPTION_PERSON.NM_UPR_CURR_LST, ' fuzzy({A}, 68, 6, weight) ', 1) >0
From my research SQL Server does have a CONTAINS function, but it's not quite the same as Oracle's. From the research I've done I can't seem to figure out what the ', 1' in the Oracle query signifies, therefore I'm not sure how it would translate in the SQL world.
The CONTAINS clause in Oracle has the following syntax:
fuzzy(term, score, numresults, weight)
Parameter Description
term The word on which to perform the fuzzy expansion. Oracle Text expands term to include words only in the index.
score The similarity score. Terms in the expansion that score below this are discarded. The default is 60 and the max is 80.
numresults The maximum number of terms to use in the expansion of term. Use a number between 1 and 5000. The default is 100.
weight Specify WEIGHT or W for the results to be weighted according to their similarity scores. Specify NOWEIGHT or N for
no weighting of results.
In trying to start simple with the conversion, I tryed:
SELECT NM_UPR_CURR_LST /* COUNT(*) */ FROM ADOPTION_PERSON WHERE
CONTAINS(NM_UPR_CURR_LST, 'A')
The result:
NM_UPR_CURR_LST
--------------------
Informational: The full-text search condition contained noise word(s).
Any help would be very greatly appreciated.
Thanks,
Dave
April 18, 2008 at 11:04 am
David.Mizelle (4/18/2008)
I'm trying to convert the following query from Oracle to SQL Server.SELECT COUNT(1) NO_OF_ROWS FROM ADOPTION_PERSON WHERE (
CONTAINS(ADOPTION_PERSON.NM_UPR_CURR_LST, ' fuzzy({A}, 68, 6, weight) ', 1) >0
From my research SQL Server does have a CONTAINS function, but it's not quite the same as Oracle's. From the research I've done I can't seem to figure out what the ', 1' in the Oracle query signifies, therefore I'm not sure how it would translate in the SQL world.
The CONTAINS clause in Oracle has the following syntax:
fuzzy(term, score, numresults, weight)
Parameter Description
term The word on which to perform the fuzzy expansion. Oracle Text expands term to include words only in the index.
score The similarity score. Terms in the expansion that score below this are discarded. The default is 60 and the max is 80.
numresults The maximum number of terms to use in the expansion of term. Use a number between 1 and 5000. The default is 100.
weight Specify WEIGHT or W for the results to be weighted according to their similarity scores. Specify NOWEIGHT or N for
no weighting of results.
In trying to start simple with the conversion, I tryed:
SELECT NM_UPR_CURR_LST /* COUNT(*) */ FROM ADOPTION_PERSON WHERE
CONTAINS(NM_UPR_CURR_LST, 'A')
The result:
NM_UPR_CURR_LST
--------------------
Informational: The full-text search condition contained noise word(s).
Any help would be very greatly appreciated.
Thanks,
Dave
This looks an awful lot like SQL Server's full-text indexing stuff. You can then use things like CONTAINS, FREETEXT... with weighting options, etc...
Start reading here - I'm not an expert at it by any stretch of the imagination....
http://msdn2.microsoft.com/en-us/library/ms142547.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 18, 2008 at 12:17 pm
Thanks for the URL.
I've noticed that the CONTAINS and FREETEXT functions seem to be where I need to be. I'm new to FTS stuff myself, therefore it's all greek to me.
Thanks and I'll let everyone know what I come up with.
Thanks and have a great weekend,
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply