Special Characters

Question

Special Characters

jim.rasmussen

SSC Eights!

Points: 999
More actions
April 27, 2009 at 3:45 pm

#220607

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004282 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jim.rasmussen SSC Eights! Points: 999 More actions · Answer 2

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

Thanks

Jim

Jeff Moden SSC Guru Points: 1004282 More actions · Answer 3

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004282 More actions · Answer 4

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

[font="Courier New"]

--===== 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[/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.

http://www.sqlservercentral.com/articles/TSQL/62867/

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

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004282 More actions · Answer 5

Any feedback on this, Jim?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen.Richardson Right there with Babe Points: 758 More actions · Answer 6

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*

GSquared SSC Guru Points: 260824 More actions · Answer 7

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

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 8

Steve Jones - SSC Editor

SSC Guru

Points: 736063

December 27, 2010 at 2:39 pm

#1266588

closing this thread. Please check dates before replying.