May 26, 2011 at 3:33 am
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
May 26, 2011 at 12:30 pm
You just need to use nested replace statements.
replace(replace(MyCol, '&', ' '), '@', ' ')
just add as many nested replace calls as you need.
_______________________________________________________________
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/
May 26, 2011 at 12:38 pm
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.
May 26, 2011 at 1:45 pm
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/
May 26, 2011 at 2:34 pm
Notice all the posts and responses you have gotten to your identical posts? This is why we try to discourage multiple postings.
Direct all further replies for this thread here.
_______________________________________________________________
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/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply