locked
Match manufacturer by name and pull company's website address RRS feed

  • Question

  • User-718146471 posted

    Hey folks, got a quick question that I hope someone can answer. I have a list of 20K vendors and need to reference their main corporate URL. I was hoping there was some query in SQL that I could do to accomplish this. I really dread having to match up 20K vendor URLs by hand since it would take bloody ages. Please let me know your approach to this. Thank you! Again, I am trying to find the main URL for the manufacturer. (i.e. Microsoft Corporation = http://www.microsoft.com) Thanks again!

    Monday, November 28, 2016 2:46 PM

Answers

  • User-821857111 posted

    Rough and ready: 

    update suppliers set website = 'www.' + ltrim(rtrim(lower([CompanyName]))) + '.com' where charindex(' ', [CompanyName]) = 0
    update suppliers set website = 'www.' + ltrim(rtrim(lower(left([CompanyName], charindex(' ', [CompanyName]))))) + '.com' where charindex(' ', [CompanyName]) > 0
    

    I have no idea what proportion this will work for.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2016 8:09 PM

All replies

  • User-821857111 posted

    I was hoping there was some query in SQL that I could do to accomplish this.
    Not reliably. If there was always a one-to-one match between a company name and it's main domain, it could be pretty simple, but there isn't.

    Monday, November 28, 2016 4:35 PM
  • User-718146471 posted

    If there is any way this can be done, even if it gets me reasonably close, I'd rather only have to update <1000 records than the whole bunch like I have now. If you know any way this could be done, even downloading a CSV file with Vendors and their web site addresses, that would be fine. I can always tweak the records if something gets borked.

    Monday, November 28, 2016 6:26 PM
  • User-821857111 posted

    Rough and ready: 

    update suppliers set website = 'www.' + ltrim(rtrim(lower([CompanyName]))) + '.com' where charindex(' ', [CompanyName]) = 0
    update suppliers set website = 'www.' + ltrim(rtrim(lower(left([CompanyName], charindex(' ', [CompanyName]))))) + '.com' where charindex(' ', [CompanyName]) > 0
    

    I have no idea what proportion this will work for.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 28, 2016 8:09 PM
  • User-718146471 posted

    At least this will get my close so I can test the links, find out which ones work and go from there. Thanks Mike!

    Wednesday, November 30, 2016 3:00 PM