Post reply

Sql Help- Urgent

  • February 26, 2015 at 4:33 pm

    #315691

    So I need to edit some text in sql.

    I have a column that has a text such as: 'DANIEL SHAFFERS, INCORPORATED.' and another text such as:BUFFETS, INCORPOR.

    I need to remove the ',' and '.' before and after Incorporated and change the Word 'Incorporated' to just Inc

    Is there a way to do that in sql and just do it once for all the different types of way Incorporated is written out because data has 'Incorporated' spelled out as Incor or Incorp, etc.

    Please help

  • February 26, 2015 at 4:36 pm

    #1780428

    REPLACE is your friend...

  • February 26, 2015 at 4:40 pm

    #1780429

    I have used replace but it does not change all the different types of Incorporated that are listed in the data. It only does it one by one and the data has 100s of different way Incorporated is spelled out.

  • February 26, 2015 at 5:14 pm

    #1780434

    It depends how your real data looks like but if there's only one comma and one dot you can do something like this:

    update yourTable

    Set YourColumn=substring(YourColumn, 1, CharIndex(',', YourColumn)-1)+' '+'INC' from YourTable

  • February 26, 2015 at 5:19 pm

    #1780437

    So you have data that's not clean? You might want to look around here for Fuzzy Matching articles. If you have SQL Server Enterprise, you can use it in SSIS to clean the data up. Look up "Fuzzy Lookup" and "fuzzy match" in the articles section - there are a bunch. If you have Enterprise, there's a Fuzzy match transformation or whatever in SSIS. (I'm terrible at SSIS, so you might want to ask someone like Koen Verbeeck and see what he says.)

  • February 26, 2015 at 6:20 pm

    #1780451

    Something like this?

    WITH SampleData AS(

    SELECT 'DANIEL SHAFFERS, INCORPORATED.' AS CompanyName UNION ALL

    SELECT 'BUFFETS, INCORPOR. ' UNION ALL

    SELECT 'WAYNE, Incor ' UNION ALL

    SELECT 'STARK, Incorp. ' UNION ALL

    SELECT 'Osborn, Co. ' UNION ALL

    SELECT 'Inception Industries '

    )

    SELECT CASE WHEN CompanyName LIKE '%,%Inc%'

    THEN LEFT( CompanyName, CHARINDEX( ',', CompanyName)) + ' Inc'

    ELSE CompanyName END

    FROM SampleData

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply