Databases
StackOverflow2013
Postsdbo
POPattern matching of websites
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPattern matching of websites
    primarykey
    data
    text
    <p>I maintain a global repository of sites in a table.</p> <pre><code>website: id, name, url 1 google http://www.google.com/ 2 CNN http://www.cnn.com/ 3 SO http://www.stackoverflow.com/ </code></pre> <p>I maintain a reference table, which stores the the website id's the user has stored.</p> <pre><code>userwebsite userid, websiteid [attributes of the table] </code></pre> <p>Say a user is interested to save microsoft; in his collection, he enters</p> <blockquote> <p>www.microsoft.com</p> </blockquote> <p>As the website doesn't exist in the global repository, it first sits in the repository and then gets added to his collection. Now the contents of both the tables looks something like this:</p> <pre><code>website: id, name, url 1 google http://www.google.com/ 2 CNN http://www.cnn.com/ 3 SO http://www.stackoverflow.com/ 4 msft http://www.microsoft.com userwebsite: userid, websiteid 1 4 </code></pre> <p>Say a user is interested in saving google in his collection, and he enters</p> <pre><code>www.google.com </code></pre> <p>As the website is already existing in the collection, instead of adding the website to the collection, only the reference gets added to the user collection.</p> <p>The place where am stuck, </p> <blockquote> <p>both www.google.com and <a href="http://www.google.com/" rel="nofollow">http://www.google.com/</a></p> </blockquote> <p>semantically they point out to the same site, but when you try to match them they are 2 distinct strings. How should I go about matching the strings in such cases?</p> <p>One solution I think of is, input a site first check if the domain exists in the collection of websites (probably a PATINDEX will do good here), by doing this you get a list of sites which have the save domain name. and then check if the path exists in any of the resultant websites. Is this is a good idea?</p> <p>Does a significant solution exist to this problem? Are there any better methods to go about?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPaul Sonier
    1. USLearnCode
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan't you somehow "normalize" (all lowercase, add http://, add the trailing slash, ...) the address before storing it into the DB?
      singulars
      1. POPattern matching of websites
      1. USMatteo Italia
    2. CO@Matteo: I have stored SO as http://stackoverflow.com in the database. Say you as a user added SO as http://www.stackoverflow.com, how will I resolve this case, and to add some more info all the URL's in my table are entered with a http://" appended to them
      singulars
      1. POPattern matching of websites
      1. USLearnCode
    3. CO@LearnCode - You will either need to add or remove the `http://` part from your addresses in order to do string matching in SQL. The string functions in SQL can only cope with simple comparison, anything more complex and they will return a mismatch. For example, sometimes you can even omit the `www.` part of an address but your browser will still find the web site. Try it with `stackoverflow.com` ;)
      singulars
      1. POPattern matching of websites
      1. USTony
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload