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.
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:REPLACEReplaces all occurrences of the second given string expression in the first string expression with a third expression.SyntaxREPLACE ( '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 TypesReturns 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.ExamplesThis example replaces the string cde in abcdefghi with xxx.SELECT REPLACE('abcdefghicde','cde','xxx')GOGreetings Günnie |
|
|
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 |
|
|
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þ = thIf you believe there are characters encoded in the range 128-159, the following also match:ƒ = f [hmm...]Š = SŒ = OEŽ = Zš = sœ = oež = zŸ = Y |
|
|
|
|
|
|
|