need help in cleaning item name

• vineetbhargav

  Mr or Mrs. 500

  Points: 522

  More actions

  May 26, 2011 at 4:21 am

  #389591

  Hi i have a table of 10 millions records,now what i want to do is i want to clean the item name, i just need to keep the space between the words and all the alphabets and numbers that it.But my problem starts where if there is "," or "/' or "-"

  and it doesnt have any space between two characters than i need to replace it with space ..for example

  below is my item name

  Alternative Apparel The Stevie Wrap in Antique White,Tops (L/S) for Women

  Result should be like this

  Alternative Apparel The Stevie Wrap in Antique White Tops L S for Women

  Please help me out and i need the query for both 2005 and 2008 and it should be lil fast as i have to run it 10 millions records.

  Thanks And Regards

  Vineet Bhargava

  vineetbhargav@gmail.com

  Thanks And Regards
  Vineet Bhargava
  vineetbhargav@gmail.com

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  May 26, 2011 at 4:32 am

  #1330140

  I suspect that the best option is coding a CLR assembly to replace the characters that don't match the pattern with a space.

  T-SQL is not the best tool for this kind of transformation, you have far more options in C#.

  Just my two cents, I'm eager to see what others suggest.

  -- Gianluca Sartori

• Brandie Tarvin

  SSC Guru

  Points: 173045

  More actions

  May 26, 2011 at 8:01 am

  #1330326

  REPLACE() is a wonderful function. You can use it a few ways. In a normal UPDATE statement, which will take quite a while for your 10 million rows. Or you can pull all the data with your special characters into a Temp table, fix it in the temp table, and join it back to the main table for the UPDATE (which may be faster).

  Using a CTE with the UPDATE and REPLACE() would also work. It just depends on how you want to go about this.

  Either way, it will probably take a while. I advise doing the cleaning in off hours or a non-production database. At the very least, you need to test it in a non-production DB. And REPLACE() can be nested, too.

  Here's example code:

  Declare @MyDesc varchar(100);

  Set @MyDesc =

  'Alternative Apparel The Stevie Wrap in Antique White,Tops (L/S) for Women';

  SELECT REPLACE(REPLACE(

  REPLACE(REPLACE(REPLACE(@MyDesc,',',' '),'-',' '),'/',' '),'(',' '),')',' ')

  , @MyDesc;

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 26, 2011 at 2:38 pm

  #1330648

  The OP posted this exact topic in another thread. Here are all the posts from that thread. :crazy:

  Sean Lange (5/26/2011)

  ---

  You just need to use nested replace statements.

  replace(replace(MyCol, '&', ' '), '@', ' ')

  just add as many nested replace calls as you need.

  PhilPacha (5/26/2011)

  ---

  If you have 'L / S', with a space before and after the '/', do you want to replace the '/' with a space, or just remove the '/'? The answer has a definite impact on the logic needed. Possibly, you could just replace the '/' with a space, then replace all occurences of two (or more) spaces with a single space. Jeff Moden has posted a nifty way to do that.

  Sean Lange (5/26/2011)

  ---

  Here is the link to Jeff's original article[/url].

  And here is an example of how killer his code is with your example (and a few extra spaces).

  declare @original varchar(100) = 'Alternative Apparel The Stevie Wrap in Antique White,Tops (L/S) for Women'

  select replace(replace(replace(@Original, '(', ' '), ')', ' '), '/', ' '),

  REPLACE(

  REPLACE(

  REPLACE(

  LTRIM(RTRIM(replace(replace(replace(@Original, '(', ' '), ')', ' '), '/', ' ')))

  ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model

  ,CHAR(7)+' ','') --Changes the XO model to nothing

  ,CHAR(7),'') AS CleanString --Changes the remaining X's to nothing

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• PhilPacha

  Hall of Fame

  Points: 3645

  More actions

  May 26, 2011 at 3:14 pm

  #1330670

  Thanks, Sean, I didn't even notice...