Replace Special Characters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Replace Special Characters

Author

Topic

joshea03
Starting Member

1 Post

Posted - 2004-07-28 : 15:28:55

I need to update a varchar value that contains special characters (for example: í to i, ñ to n, é to e). Help & Thanks in Advance

Kristen
Test

22859 Posts

Posted - 2004-07-28 : 16:21:12

Can you just CAST its COLLATION to force the extended characters to degrade (is that the right word?)

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-28 : 17:50:55

Yes, that's the same thought I had, though it's really the character set you're changing... although SQL Server lumps the two concepts together, sort of...
The thing is, most character sets have some accented Latin characters in them. So if you're going from a varchar that uses a collation whose character set is CP 1252 -- and if you've haven't changed the server/database/column collations, you probably have.[1] In that case, you might be able to get away with using Greek_CS_AI. However, while it will degrade accented Latin characters to unaccented ones, it will turn quite a few other characters with CP 1252 code points in the 128-255 range into question marks. Mostly weird punctuation, but there are a few borderline Latin letters in that group: ŒœÆÐÞßæðþ

The other problem you may have is that SQL Server isn't keen on you changing the collation of a value more than once in a query, so you can get this error popping up:

Server: Msg 445, Level 16, State 2, Line 1
COLLATE clause cannot be used on expressions containing a COLLATE clause.
--
[1] Er, sorry, I think this sentence lost the plot!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-28 : 18:04:38

Replace - Books Online(??):

UPDATE table
SET column1 = REPLACE(column1 ,'í','i')

You can wrap these or create one function that does it all for you. For example (not using function):

UPDATE table
SET column1 = REPLACE(REPLACE(REPLACE(column1 ,'í','i'),'ñ','n'),'é','e')

REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.

Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )

Arguments
'string_expression1'

Is the string expression to be searched. string_expression1 can be of character or binary data.

'string_expression2'

Is the string expression to try to find. string_expression2 can be of character or binary data.

'string_expression3'

Is the replacement string expression string_expression3 can be of character or binary data.

Return Types
Returns character data if string_expression (1, 2, or 3) is one of the supported character data types. Returns binary data if string_expression (1, 2, or 3) is one of the supported binary data types.

Examples
This example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx')
GO

Here is the result set:

------------
abxxxfghixxx
(1 row(s) affected)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-28 : 20:19:47

I ran some tests in case anyone is interested.


Select 'íiéeñn' Collate Greek_CS_AI + 'í' 
------- 
iieenni  <-- dicritical mark on trailing í is removed


As Arnold suspected:

Select 'íiéeñn' Collate Greek_CS_AI + 'í' Collate SQL_Latin1_General_CP850_CI_AS

Server: Msg 449, Level 16, State 1, Line 1
Collation conflict caused by collate clauses with different collation 'Greek_CS_AI' and 'SQL_Latin1_General_CP850_CI_AS'.

--Create a function to return different collation
CREATE Function f_noaccents(@string varchar(20))
Returns varchar(20)
AS
BEGIN
	return cast (@string AS varchar(20)) Collate Greek_CS_AI 
END


-- 6 seconds for 65,000 rows
Select dbo.f_noaccents('íiéeñn') + 'í'                     
-------
iieenní <-- dicritical mark on trailing í retained


--Compare with nested replace()
CREATE Function f_noaccents(@string varchar(20))
Returns varchar(20)
AS
BEGIN
	return REPLACE(REPLACE(REPLACE(@string ,'í','i'),'ñ','n'),'é','e')
END

-- 6 seconds for 65,000 rows
Select dbo.f_noaccents('íiéeñn') + 'í'                     
-------
iieenní <-- dicritical mark on trailing í retained


-- Inline REPLACE 4 seconds for 65,000 rows
UPDATE table
SET column1 = REPLACE(REPLACE(REPLACE(column1 ,'í','i'),'ñ','n'),'é','e')

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources