Replace Bullet Special Characters 1

[SgtJarrow]

I have a column in on table that the users are copying and pasting bulletted lists from M$ Word. These bullets are stored in the column as three special characters (•) and I want to replace them with •

But trying to use SQL REPLACE, I get:

Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.

I have a tried a couple different ways, and I think I might be able to fix this with a COLLATE hint on my column, but I don't know what to COLLATE it to. Anyone have any suggestions on how to replace this????

Thanks if you can...

SELECT
	'Students enrolled in [our] diploma programs are billed by the academic year. Some programs are provided during a single academic year and some programs are provided across two academic years. A student withdrawing from a program receives a pro-rata refund based on the percentage of the student’s completion of her then current academic year as follows:  • During the first 10% of the academic year for which the student has been charged, the school shall refund 90% of tuition.  • After 10% but within 20% of the academic year for which the student has been charged, the school shall refund 80% of tuition.  • After 20% but within 30% of the academic year for which the student has been charged, the school shall refund 70% of tuition.  • After 30% but within 40% of the academic year for which the student has been charged, the school shall refund 60% of tuition.  • After 40% but within 50% of the academic year for which the student has been charged, the school shall refund 50% of tuition.  • After 50% but within 60% of the academic year for which the student has been charged, the school shall refund 40% of tuition.  • After 60% of the academic year for which the student has been charged, the school shall retain 100% of tuition.  If a student withdraws, in addition to tuition, the student is responsible for the registration fee and book, materials and laboratory charges which are not subject to the pro rata refund calculation.  If all books are returned in new and unused condition within 20 days after the date of withdrawal, a student receives a refund of the book charges.  Uniform charges are not refundable once a uniform has been issued to a student.  If a student withdraws after three days from signing the enrollment agreement but before starting classes, non-refundable fees regarding admission and registration do not exceed $150.00.   If a student withdraws from school, any refund due to the student is paid within 45 days of the earliest of the (1) date on which student informed the school of his/her withdrawal; (2) date on which the school determines that the student dropped out; or (3) end of the semester in which the student withdrew.  If a student does not return to the school at the expiration of an approved leave of absence, refunds are made within 45 days of the earlier of the date of the expiration of the leave of absence or the date the student notifies the school that the student will not be returning to the school after the expiration of an approved leave of absence.   The student is responsible for paying any balance due on his account after a withdrawal from [us]',
	'###',
	REPLACE('###', '###', '•')
	
SELECT
	EnrollmentPolicy,
	REPLACE(EnrollmentPolicy, '•', '•')
FROM EnrollmentPrograms
--WHERE ProgramId = 19
WHERE EnrollmentPolicy LIKE '%•%'

SELECT
	EnrollmentPolicy,
	REPLACE(REPLACE(REPLACE(REPLACE(EnrollmentPolicy, 'â', '#'), '€', '#'), '¢', '#'), '###', '•')
FROM EnrollmentPrograms
--WHERE ProgramId = 19
WHERE EnrollmentPolicy LIKE '%•%'

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

Collations will not help you here. The problem is that you are using the Text data type for your column.

What version of SQL Server are you using? If you are using SQL Server 2000, then you need to learn how to use TextPTR, UpdateText, etc....

If you are using SQL Server 2005 or newer, you should probably change the data type of your column to varchar(max) instead of Text. The replace function works with varchar(max) so you won't have any problems.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

Spot on as usual George. I was hoping for you to answer. Thanks.

I am using a CAST to VARCHAR(MAX) for the moment while I request the column to be modified.

But that worked exactly as you mentioned.

And I am using SQL 2008 R2 by the way.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

There are certain data types that you should no longer be using with newer versions of SQL Server. Specifically, you should not be using Text, nText, or Image. You can use SQLCop to find all instances of columns that use this data type. There is absolutely no danger in converting the data types unless you are also using the SQL2000 frunctions like TextPTR, UPDATETEXT, WRITETEXT, or READTEXT.

You can download SQLCop from the link in my signature.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom