Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2012
    Posts
    7

    Unanswered: How to remove a string from another

    Hey,

    Quick question we just went with a new CRM. The issue being we have a category field that has Business;Client;Stuff. I would like to remove just the Business and leave the other stuff. Ive tried the link below and it removes everything... What am I doing wrong?

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business', NULL)

    Thanks

  2. #2
    Join Date
    Oct 2012
    Posts
    7
    Also if anyone could let me know how to replace a whole column from a back up that would be great. I would think it would be written like

    Use CRMDATA

    update dbo.TBL_CONTACT
    set CATEGORY = [Dummy database] dbo.TBL_CONTACT CATEGORY

    But this is a no go.

    Thanks

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    update dbo.TBL_CONTACT
    set CATEGORY = OldData.CATEGORY
    from dbo.TBL_CONTACT
    inner join [RestoredDatabase].dbo.TBL_CONTACT as OldData on TBL_CONTACT.PrimaryKey = OldData.PrimaryKey
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2012
    Posts
    7
    Hmm Im returned 0 Rows effected, the first value in both tables is NULL would this effect this string? Ive attached my edited version, I bet im just missing something stupid...

    myACT= live data
    dummydata= restored data from backup

    use myACT

    update dbo.TBL_CONTACT
    set CATEGORY = OldData.CATEGORY
    from dbo.TBL_CONTACT
    inner join [Dummy database].dbo.TBL_CONTACT as OldData on TBL_CONTACT.CATEGORY = OldData.CATEGORY

  5. #5
    Join Date
    Oct 2012
    Posts
    7
    Wait I guess I dont understand what a primary key is SQL PRIMARY KEY Constraint tells us how to create one but do I need to or can I just find it somewhere? Sorry SQL is not my strong suit.

  6. #6
    Join Date
    Oct 2012
    Posts
    7
    Any help would be great, I understand my questions are not very intelligent but I would appreciate someone coming down to my level and helping me getting this figured out.

    Thanks

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In SQL, NULL and an empty string are very different things! Enjoy!
    Code:
    CREATE TABLE #f (                   -- Just a place to put stuff
       MyColumn     NVARCHAR(20)        NOT NULL
       )
    
    INSERT INTO #f
       SELECT 'foohead' UNION
       SELECT 'tailfoo' UNION
       SELECT 'midfoodle' UNION
       SELECT 'nofu' UNION
       SELECT 'You sir, are a fool!'
    
    SELECT * FROM #f
    
    UPDATE #f
       SET MyColumn = REPLACE(myColumn, 'foo', '')
    
    SELECT * FROM #f   
    
    DROP TABLE #f
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can't join on NULLs, either inclusively or exclusively.
    Null represents "Unknown", and how could you ever make the claim that two unknown values are equal to each other, or not equal to each other?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Oct 2012
    Posts
    7
    Oh okay thanks now I understand! Okay I restored the whole database again, I am back to where i started. So I have this column with

    Business;account;people
    Business;Timsaccount;rep
    business
    Null
    Business

    Ive used the following query and ended up wiping everything in that column. what can I modify to make this work just to remove all sign of business?

    select CATEGORY from dbo.TBL_COMPANY where CATEGORY like '%Business%'

    update dbo.TBL_COMPANY
    set CATEGORY = REPLACE(CATEGORY, 'Business', NULL)

    select CATEGORY from dbo.TBL_CONTACT where CATEGORY like '%Business%'

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business', NULL)


    Again thanks for all your help and patience!

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    UPDATE dbo.TBL_COMPANY
       SET CATEGORY = REPLACE(CATEGORY, 'Business', '')
       WHERE  CATEGORY LIKE '%Business%'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Oct 2012
    Posts
    7
    Haha thanks that makes it a lot clearner I just finished mine...

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'robs Account;Business', 'robs Account')

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business;Christmas Card', 'Christmas Card')

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business;Competitor', 'Competitor')

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business;dans Account', 'dans Account')

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business;Irrigation Districts', 'Irrigation Districts')

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business;Supplier', 'Supplier')

    update dbo.TBL_CONTACT
    set CATEGORY = REPLACE(CATEGORY, 'Business', '')

    Hey but thanks for the help!

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ummm... Let me know how that works for you. I suspect that you have the logic backwards.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •