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)
 Replacing characters in a textfield

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-21 : 09:14:26
mats writes "I would like to replace all the swedish characters åäöÅÄÖ with aaoAAO in a database field defined as Varchar(255).
What's the easiest way to do this ?"

Günnie
Starting Member

4 Posts

Posted - 2002-03-21 : 10:10:43
I guess matt could use the Replace statement:

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


Greetings Günnie
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-21 : 11:33:12
If you are on SQL Server 2000, your varchar(255) column is case insensitive, and you don't want to mangle the case in the replace, you could use this sort of thing (actually, this will remove all accents* from As and Os:

UPDATE tbl SET col = REPLACE(REPLACE(REPLACE(REPLACE(
col COLLATE Latin1_General_CS_AI,
'A', 'A'), 'a', 'a'),
'o', 'o'), 'O', 'O')

It will also convert Ø to O (but leave Æ unmolested) but if you're not Danish or Norwegian this probably won't bother you

* sorry, I know that's not right, but that's what it thinks it's doing!


Edited by - Arnold Fribble on 03/21/2002 11:45:13
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-21 : 12:14:16
Just for the sake of completeness, note that using the Latin1_General_CS_AI collation, the following slightly less obvious matches are made:

Æ = AE
Ð = D
Ø = O
Þ = TH
ß = ss
æ = ae
ð = d
ø = o
þ = th

If you believe there are characters encoded in the range 128-159, the following also match:

ƒ = f [hmm...]
Š = S
Π= OE
Ž = Z
š = s
œ = oe
ž = z
Ÿ = Y


Go to Top of Page
   

- Advertisement -