InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:13:37 GMT20http://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspxclosing this thread. Please check dates before replying.Mon, 27 Dec 2010 14:39:20 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspxStephen, you do realize this question was from May, 2 years ago (2009), right?Mon, 27 Dec 2010 14:13:47 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspxNot totaly clear about if you wanted to remove all spaces or just leading?? If you are tataly striping the characters just use a series of nested "REPLACE" statements.DECLARE @str1 VARCHAR(100), @str2 VARCHAR(100), @str3 VARCHAR(100) SET @str1 = 'A & B Cleaners'SET @str2 = 'A and B Cleaners'SET @str3 = 'Cleaners A & B'SELECT '*'+@str1+'*', '*'+REPLACE(REPLACE(@str1, ' ', ''), '&', '')+'*'SELECT '*'+@str2+'*', '*'+REPLACE(REPLACE(@str2, ' ', ''), '&', '')+'*'SELECT '*'+@str3+'*', '*'+REPLACE(REPLACE(@str3, ' ', ''), '&', '')+'*'String Result*A & B Cleaners* *ABCleaners**A and B Cleaners* *AandBCleaners**Cleaners A & B* *CleanersAB*Mon, 27 Dec 2010 14:07:41 GMTStephen.Richardsonhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspxAny feedback on this, Jim?Fri, 01 May 2009 21:32:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspxOk, here we go... Assuming that you have the company name in a variable and the fact that you're using a version of T-SQL that cannot use functions, here's how I'd do it...[font="Courier New"][size="2"][color="black"][/color][color="green"]--===== Company name is in a variable[/color][color="blue"]DECLARE [/color][color="#434343"]@CompanyName [/color][color="blue"]VARCHAR[/color][color="gray"]([/color][color="black"]256[/color][color="gray"]) [/color][color="blue"]SELECT [/color][color="#434343"]@CompanyName [/color][color="blue"]= [/color][color="red"]'A & B Cleaners'   [/color][color="blue"]PRINT [/color][color="#434343"]@CompanyName [/color][color="green"]--Just for verification... you can remove this line--===== Using a "Tally" table as a loop driver, remove all characters that     -- are NOT in the in range of A to Z (upper or lower case) [/color][color="blue"]SELECT [/color][color="#434343"]@CompanyName [/color][color="blue"]= [/color][color="magenta"]STUFF[/color][color="gray"]([/color][color="#434343"]@CompanyName[/color][color="gray"],[/color][color="magenta"]PATINDEX[/color][color="gray"]([/color][color="red"]'%[^A-Z]%'[/color][color="gray"],[/color][color="#434343"]@CompanyName[/color][color="gray"]),[/color][color="black"]1[/color][color="gray"],[/color][color="red"]''[/color][color="gray"])   [/color][color="blue"]FROM [/color][color="black"]dbo.Tally t  [/color][color="blue"]WHERE [/color][color="black"]t.N [/color][color="gray"]<= [/color][color="magenta"]LEN[/color][color="gray"]([/color][color="#434343"]@CompanyName[/color][color="gray"])    AND [/color][color="magenta"]SUBSTRING[/color][color="gray"]([/color][color="#434343"]@CompanyName[/color][color="gray"],[/color][color="black"]t.N[/color][color="gray"],[/color][color="black"]1[/color][color="gray"]) LIKE [/color][color="red"]'[^A-Z]'[/color][color="green"]--===== Grab just the left six characters of what remains. [/color][color="blue"]SELECT [/color][color="#434343"]@CompanyName [/color][color="blue"]= [/color][color="magenta"]LEFT[/color][color="gray"]([/color][color="#434343"]@CompanyName[/color][color="gray"],[/color][color="black"]6[/color][color="gray"])[/color][color="green"]--===== Display the result (just for verification... you can remove this line)  [/color][color="blue"]PRINT [/color][color="#434343"]@CompanyName[/color][/size][/font]If you don't have a Tally table, please see the article at the following URL for how to build one, what it is, and how it works. It's a very useful tool that can frequently be used to replace loops. [url]http://www.sqlservercentral.com/articles/TSQL/62867/[/url]If you need to do this to a whole table column, please post back.Wed, 29 Apr 2009 19:30:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspx[quote][b]jim.rasmussen (4/28/2009)[/b][hr]Yes it does. I have researched this, but can't seem to make it work correctly. Any thoughts?ThanksJim[/quote]Sorry, Jim... I had almost 900 emails in my inbox since I posted this... I'll be back. Thanks for the info.Wed, 29 Apr 2009 17:58:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspxYes it does. I have researched this, but can't seem to make it work correctly. Any thoughts?ThanksJimTue, 28 Apr 2009 07:42:02 GMTjim.rasmussenhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspx[quote][b]jim.rasmussen (4/27/2009)[/b][hr]I have a filed of varchar the holds names of companies. Like 'A & B Cleaners'. What I what to do is to select this.. 'ABClea'. How can I get this done?ThanksJim[/quote]Wow... that does go back a bit...Does your version of SQL Server have the STUFF function available?Mon, 27 Apr 2009 19:46:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic705393-1437-1.aspxI have a filed of varchar the holds names of companies. Like 'A & B Cleaners'. What I what to do is to select this.. 'ABClea'. How can I get this done?ThanksJimMon, 27 Apr 2009 15:45:15 GMTjim.rasmussen