in fox este ceva procedura "similar" cu care verifici cat la suta seamana o inreg cu alta

cam asa:

SELECT title, similar(title,searchTerm) FROM movies WHERE similar(title,searchTerm) > 55 

mie imi trebuie o procedura stocata care verifica cat seamana o inreg cu alta(in procente) 

aveti careva una ?

vi-as fii recunoscator 

 sau un stored procedure pt insert care verifica daca exista ceva similar in baza si daca nu exista introduce

Aruncati o privire aici:

Understanding Ranking

Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued Functions

 

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro

m-am uitat

mie mi-ar trebuie o functie stocata care sa gaseasca cel mai lung  common substring  cu 2 parametri

ceva e genul asta in fox:

FUNCTION FindCommon(tcS1,tcS2)
* Finds longest common substring (other than single
* characters) in tcS1 and tcS2, then recursively
* finds longest common substring in left-hand
* portion and right-hand portion. Updates the
* cumulative score.

LOCAL lnLongest, lnStartPos1, lnStartPos2, lnJ
LOCAL lcHoldStr, lcTestStr, lcLeftStr1, lcLeftStr2
LOCAL lcRightStr1, lcRightStr2

STORE 0 TO lnLongest, lnStartPos1, lnStartPos2

lcHoldStr = tcS2
DO WHILE LEN(lcHoldStr)>lnLongest

  lcTestStr = lcHoldStr
  DO WHILE LEN(lcTestStr)>1
    lnJ = AT(lcTestStr,tcS1)
    IF lnJ > 0
      * Test string is sub-set of the other string
     
      IF LEN(lcTestStr) > lnLongest
        * Test string is longer than previous
        * longest. Store its length and position.
        lnLongest = LEN(lcTestStr)
        lnStartPos1 = lnJ
        lnStartPos2 = AT(lcTestStr,tcS2)
      ENDIF
               
      * No point in going further with this string
      EXIT
       
    ELSE
      * Test string is not a sub-set of the other
      * string. Discard final character of test
      * string and try again.
      lcTestStr = LEFT(lcTestStr,LEN(lcTestStr)-1)
    ENDIF

  ENDDO

  * Now discard first char of test string and
  * repeat the process.
  lcHoldStr = RIGHT(lcHoldStr,LEN(lcHoldStr)-1)

ENDDO

* Update the cumulative score with the length of
* the common sub-string.
pnScore = pnScore + lnLongest

* We now have the longest common sub-string, so we
* can isolate the sub-strings to the left and right
* of it.

lcLeftStr1 = LEFT(tcS1,lnStartPos1-1)
lcLeftStr2 = LEFT(tcS2,lnStartPos2-1)
lcRightStr1 = SUBSTR(tcS1,lnStartPos1+lnLongest)
lcRightStr2 = SUBSTR(tcS2,lnStartPos2+lnLongest)

IF NOT EMPTY(lcLeftStr1) AND NOT EMPTY(lcLeftStr2)
  * Get longest common substring from left strings
  FindCommon(lcLeftStr1,lcLeftStr2)
ENDIF

IF NOT EMPTY(lcRightStr1) AND NOT EMPTY(lcRightStr2)
  * Get longest common substring from right strings
  FindCommon(lcRightStr1,lcRightStr2)
ENDIF

RETURN

ENDFUNC 

 

ar cunostintele mele de sql sunt cam putine 

O solutie de-a gata: Finding the “Longest Common Substring” in SQL 2000

Un potential start daca solutia va nemultumeste: Intelligent Database Design Using Hash Keys

Si o sugestie daca stiti C# puteti sa implementati o functie scrisa in .NET.

 

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro

In articolul "Finding the “Longest Common Substring” in SQL 2000" amintit de xmldev se sugereaza destul de clar ca solutia respectiva se preteaza la un nr. mic de inregistrari (de altfel cred ca nici se poate avea alte pretentii de la o functie care nu este nativa).

Abandon: cred ca functionalitatea ceruta de tine se preteaza cel mai bine pentru implementarea pe partea de client (dupa o filtrarea prealabila a inregistrarilor) folosind un limbaj de programare care eventual pune la dispozitie niste functii pentru lucrul cu siruri de caractere: C# & co.

 

mersi baieti f mult

mai am de facut functia asta:

FUNCTION Similar (tcStr1, tcStr2) 
* Compares two strings. Returns a numeric
* indication of their similarity (0 = not
* at all similar, 100 = identical).

* Written by Mike Lewis, June 2002

LOCAL lcOK
PRIVATE pnScore

* Convert each string to simplest form (letters
* and digits only, all upper case)
tcStr1 = UPPER(tcStr1)
tcStr2 = UPPER(tcStr2)
lcOK = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
tcStr1=CHRTRAN(tcStr1,CHRTRAN(tcStr1,lcOK,""),"")
tcStr2=CHRTRAN(tcStr2,CHRTRAN(tcStr2,lcOK,""),"")

IF EMPTY(tcStr1) OR EMPTY(tcStr2)
  * One or both of the strings is now empty
  RETURN 0
ENDIF

IF tcStr1 == tcStr2
  * Strings are identical
  RETURN 100
ENDIF

* Initialize cumulative score (this will be the
* total length of all the common substrings)
pnScore = 0

* Find all common sub-strings
FindCommon(tcStr1,tcStr2)

* We now have the cumulative score. Return this
* as a percent of the maximum score. The maximum
* score is the average length of the two strings.
RETURN pnScore*200 /(LEN(tcStr1)+LEN(tcStr2))

ENDFUNC 

 

da parca ar fii mai indicat sa o fac in C# ,de fap pe amandoua

dar exista o fct similar si stored procedure? 

Exemple implementare in C# & VB.NET

Algorithm implementation/Strings/Longest common subsequence

The Longest Common Substring with Maximal Consecutive