Special Characters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Older Versions of SQL (v6.5, v6.0, v4.2) » Older Versions of SQL (v6.5, v6.0, v4.2) » Special Characters

Special Characters

Rate Topic

Display Mode

Topic Options

Author

Message

jim.rasmussen

Posted Monday, April 27, 2009 3:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 14, 2012 6:45 AM
Points: 49, Visits: 122

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?
Thanks
Jim

Post #705393

Jeff Moden

Posted Monday, April 27, 2009 7:46 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

jim.rasmussen (4/27/2009)

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?
Thanks
Jim

Wow... that does go back a bit...

Does your version of SQL Server have the STUFF function available?

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #705466

jim.rasmussen

Posted Tuesday, April 28, 2009 7:42 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 14, 2012 6:45 AM
Points: 49, Visits: 122

Yes it does. I have researched this, but can't seem to make it work correctly. Any thoughts?
Thanks
Jim

Post #705815

Jeff Moden

Posted Wednesday, April 29, 2009 5:58 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

jim.rasmussen (4/28/2009)

Yes it does. I have researched this, but can't seem to make it work correctly. Any thoughts?
Thanks
Jim

Sorry, Jim... I had almost 900 emails in my inbox since I posted this... I'll be back. Thanks for the info.

--Jeff Moden

Post #707306

Jeff Moden

Posted Wednesday, April 29, 2009 7:30 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

Ok, 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...

--===== Company name is in a variable
DECLARE @CompanyName VARCHAR(256)
SELECT @CompanyName = 'A & B Cleaners'
  PRINT @CompanyName --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)
SELECT @CompanyName = STUFF(@CompanyName,PATINDEX('%[^A-Z]%',@CompanyName),1,'')
   FROM dbo.Tally t
  WHERE t.N <= LEN(@CompanyName)
    AND SUBSTRING(@CompanyName,t.N,1) LIKE '[^A-Z]'

--===== Grab just the left six characters of what remains.
SELECT @CompanyName = LEFT(@CompanyName,6)

--===== Display the result (just for verification... you can remove this line)
  PRINT @CompanyName

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.
http://www.sqlservercentral.com/articles/TSQL/62867/

If you need to do this to a whole table column, please post back.

--Jeff Moden

Post #707325

Jeff Moden

Posted Friday, May 01, 2009 9:32 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

Any feedback on this, Jim?

--Jeff Moden

Post #708861

Stephen.Richardson

Posted Monday, December 27, 2010 2:07 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, December 21, 2012 6:44 AM
Points: 26, Visits: 133

Not 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*

Post #1039577

GSquared

Posted Monday, December 27, 2010 2:13 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

Stephen, you do realize this question was from May, 2 years ago (2009), right?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1039582

Steve Jones - SSC Editor

Posted Monday, December 27, 2010 2:39 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:54 PM
Points: 31,410, Visits: 13,726

closing this thread. Please check dates before replying.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1039592

« Prev Topic | Next Topic »

Permissions