Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    Unanswered: Remove keyword with twins

    Hi guys,

    I'm writing the stored procedure to remove some invalid names like below example of unclean data. Those data have eg: 1st Twin, 2nd twin, (twin) , need to be removed. Any sql statement or function able to do that?

    Code:
    1st TwinYap Ker Qi
    2nd Twin Yap Ker Li
    1st Twin Lim Wee Jie
    2nd Twin Lim Wee Zhe
    Joshua Rizki,Twin1
    Jashua Rizki,Twin 2
    Ray Robert Lim(Twin 1)
    Ryan Robert Lim(Twin 2)
    Twin II
    Twin  I
    Wong Guanhao, Twin 1
    Wong Guanjie, Twin 2
    Hannah Kaiyisah/sarah Najwa - Twin
    Tan Mei Xin (1st Twin)
    Wan Iqbal Ariff (twin)
    Wan Aisya Nabila (twin)
    1st Twin Tan Jing Wen
    Chan Jun Yen  2nd Twin
    Low Cheng Jun  1st Twin
    Low Cheng Kang  2nd Twin
    Ding Xin Jue (twin)
    Ding Zhi Yi (twin)
    2nd Twin Heng Xi Ru
    1st Twin Heng Xi Wen
    Anis Batrisyia(1st Twin)
    Ammar Danial(2nd Twin)
    Alden Alham (twin)
    Arvin Aariz (twin)
    Chiew Jun Yi (twin)
    Chiew Jun Chi (twin)
    Muhammad Faris Radznan (twin)
    Mok Wing Ci  1st Twin
    Mok Wing Sum    2nd Twin
    Gabrielle Lim  (2nd Twin)
    Muhammad Harith Razman (twin)
    1st Twin Claire Foo
    2nd Twin Clarance Foo

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    WHERE  PatIndex('%twin%', name) > 0
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Posts
    8
    Quote Originally Posted by georgev
    Code:
    WHERE  PatIndex('%twin%', name) > 0
    that would remove "Twin" , how about the 1st,2nd, (brackets)??

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ohh, so you want to Replace() the value of "twin" etc?
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2008
    Posts
    8
    i wan to remove those keyword that has 1st twin,2nd twin,(twin 1),(twin), twin 1st,twin 2nd.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    DELETE the records containing the word TWIN?
    UPDATE the records, REMOVING the word TWIN?
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Without a good rule to determine the difference between a name, and the garbage words (twin, 1st, first, etc.), you will not be able to do this in one pass. Just imagine John Smith III's surprise, when two entire generations of his name get cut off by a wayward program. Best bet is to have a human go through, and update each record individually, AND have the business impose stiff penalties on anyone entering such garbage ever again.

Posting Permissions

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