replacing a strange character
-
I have a special character in my table that I want to replace any where it is (while extracting the data). I do not even know how to replicate that character here to show it to you all. It's like a slanted " over the letters (like over A, B, etc) and it's generally there for Spanish names. I want to take it out.
How can I do this? Thanks!
-
It's this character. I was able to copy it.
Ñ
Thanks!
-
SELECT REPLACE('ÑiÑo', 'Ñ', 'n')
-
But how can I do it for all characters? I need to search for any letter (Ato Z) that has that special character and then take it out.
-
Replace can only search one string at the time... you'll have to use multiple replaces :
replace(replace(replace(YourCol, 'é', 'e'), 'è', 'e'), 'ê', 'e')
-
On a more basic note. Why do you want to alter the data exactly?
-
I have to extract data from this table and load it into SAP which gives me an error when it finds this character. I could override it in SAP but that would effect all users, so I don't want to do that. Thanks.
-
Can I ask you something? How did you type the special character? What did you use on the key board? I know this sounds silly!
Thanks!
-
Copy paste
. -
this might help: each of the characters can be used in a replace or something; SELECT char(169) returns ©
for example.
DEC HEX CHAR 33 21 ! 34 22 " 35 23 # 36 24 $ 37 25 % 38 26 & 39 27 ' 40 28 ( 41 29 ) 42 2A * 43 2B + 44 2C , 45 2D - 46 2E . 47 2F / 48 30 0 49 31 1 50 32 2 51 33 3 52 34 4 53 35 5 54 36 6 55 37 7 56 38 8 57 39 9 58 3A : 59 3B ; 60 3C < 61 3D = 62 3E > 63 3F ? 64 40 @ 65 41 A 66 42 B 67 43 C 68 44 D 69 45 E 70 46 F 71 47 G 72 48 H 73 49 I 74 4A J 75 4B K 76 4C L 77 4D M 78 4E N 79 4F O 80 50 P 81 51 Q 82 52 R 83 53 S 84 54 T 85 55 U 86 56 V 87 57 W 88 58 X 89 59 Y 90 5A Z 91 5B [ 92 5C \ 93 5D ] 94 5E ^ 95 5F _ 96 60 ` 97 61 a 98 62 b 99 63 c 100 64 d 101 65 e 102 66 f 103 67 g 104 68 h 105 69 i 106 6A j 107 6B k 108 6C l 109 6D m 110 6E n 111 6F o 112 70 p 113 71 q 114 72 r 115 73 s 116 74 t 117 75 u 118 76 v 119 77 w 120 78 x 121 79 y 122 7A z 123 7B { 124 7C | 125 7D } 126 7E ~ 145 91 ‘ 146 92 ’ 161 A1 ¡ 162 A2 ¢ 163 A3 £ 164 A4 ¤ 165 A5 ¥ 166 A6 ¦ 167 A7 § 168 A8 ¨ 169 A9 © 170 AA ª 171 AB « 172 AC ¬ 173 AD 174 AE ® 175 AF ¯ 176 B0 ° 177 B1 ± 178 B2 ² 179 B3 ³ 180 B4 ´ 181 B5 µ 182 B6 ¶ 183 B7 · 184 B8 ¸ 185 B9 ¹ 186 BA º 187 BB » 188 BC ¼ 189 BD ½ 190 BE ¾ 191 BF ¿ 192 C0 À 193 C1 Á 194 C2 Â 195 C3 Ã 196 C4 Ä 197 C5 Å 198 C6 Æ 199 C7 Ç 200 C8 È 201 C9 É 202 CA Ê 203 CB Ë 204 CC Ì 205 CD Í 206 CE Î 207 CF Ï 208 D0 Ð 209 D1 Ñ 210 D2 Ò 211 D3 Ó 212 D4 Ô 213 D5 Õ 214 D6 Ö 215 D7 × 216 D8 Ø 217 D9 Ù 218 DA Ú 219 DB Û 220 DC Ü 221 DD Ý 222 DE Þ 223 DF ß 224 E0 à 225 E1 á 226 E2 â 227 E3 ã 228 E4 ä 229 E5 å 230 E6 æ 231 E7 ç 232 E8 è 233 E9 é 234 EA ê 235 EB ë 236 EC ì 237 ED í 238 EE î 239 EF ï 240 F0 ð 241 F1 ñ 242 F2 ò 243 F3 ó 244 F4 ô 245 F5 õ 246 F6 ö 247 F7 ÷ 248 F8 ø 249 F9 ù 250 FA ú 251 FB û 252 FC ü 253 FD ý 254 FE þ 255 FF ÿ Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here's a quick, brute force way. It involves creating a table to map characters with ascii codes above 127 to other characters. You could create CharMap as a temp table or table variable if you like. In fact, it could be included in the UDF. I'd rather have a permanent table if I'm going to reference it many times. At first, I created a set-based UDF, but that required an additional UDF (fn_Split, which can be found on this site) to convert the string into a table.
Anyway, next, create the UDF FixChars().
Finally, see the sample execution at the end.
---------------------------------------------------------------------------
DROP TABLE CharMap
GO
CREATE TABLE CharMap
(
prevAscii tinyint
, prevChar varchar(1)
, newChar varchar(2)
)
GO
INSERT CharMap (prevAscii, prevChar, newChar)
SELECT 138, 'Š', 'S'
UNION ALL
SELECT 142, 'Ž', 'Z'
UNION ALL
SELECT 158, 'ž', 'z'
UNION ALL
SELECT 159, 'Ÿ', 'Y'
UNION ALL
SELECT 192, 'À', 'A'
UNION ALL
SELECT 193, 'Á', 'A'
UNION ALL
SELECT 194, 'Â', 'A'
UNION ALL
SELECT 195, 'Ã', 'A'
UNION ALL
SELECT 196, 'Ä', 'A'
UNION ALL
SELECT 197, 'Å', 'A'
UNION ALL
SELECT 198, 'Æ', 'AE'
UNION ALL
SELECT 199, 'Ç', 'C'
UNION ALL
SELECT 200, 'È', 'E'
UNION ALL
SELECT 201, 'É', 'E'
UNION ALL
SELECT 202, 'Ê', 'E'
UNION ALL
SELECT 203, 'Ë', 'E'
UNION ALL
SELECT 204, 'Ì', 'I'
UNION ALL
SELECT 205, 'Í', 'I'
UNION ALL
SELECT 206, 'Î', 'I'
UNION ALL
SELECT 207, 'Ï', 'I'
UNION ALL
SELECT 208, 'Ð', 'D'
UNION ALL
SELECT 209, 'Ñ', 'N'
UNION ALL
SELECT 210, 'Ò', 'O'
UNION ALL
SELECT 211, 'Ó', 'O'
UNION ALL
SELECT 212, 'Ô', 'O'
UNION ALL
SELECT 213, 'Õ', 'O'
UNION ALL
SELECT 214, 'Ö', 'O'
UNION ALL
SELECT 216, 'Ø', 'O'
UNION ALL
SELECT 217, 'Ù', 'U'
UNION ALL
SELECT 218, 'Ú', 'U'
UNION ALL
SELECT 219, 'Û', 'U'
UNION ALL
SELECT 220, 'Ü', 'U'
UNION ALL
SELECT 221, 'Ý', 'Y'
UNION ALL
SELECT 222, 'Þ', '' -- ?
UNION ALL
SELECT 223, 'ß', 'B'
UNION ALL
SELECT 224, 'à', 'a'
UNION ALL
SELECT 225, 'á', 'a'
UNION ALL
SELECT 226, 'â', 'a'
UNION ALL
SELECT 227, 'ã', 'a'
UNION ALL
SELECT 228, 'ä', 'a'
UNION ALL
SELECT 229, 'å', 'a'
UNION ALL
SELECT 230, 'æ', 'ae'
UNION ALL
SELECT 231, 'ç', 'c'
UNION ALL
SELECT 232, 'è', 'e'
UNION ALL
SELECT 233, 'é', 'e'
UNION ALL
SELECT 234, 'ê', 'e'
UNION ALL
SELECT 235, 'ë', 'e'
UNION ALL
SELECT 236, 'ì', 'i'
UNION ALL
SELECT 237, 'í', 'i'
UNION ALL
SELECT 238, 'î', 'i'
UNION ALL
SELECT 239, 'ï', 'i'
UNION ALL
SELECT 240, 'ð', 'o'
UNION ALL
SELECT 241, 'ñ', 'n'
UNION ALL
SELECT 242, 'ò', 'o'
UNION ALL
SELECT 243, 'ó', 'o'
UNION ALL
SELECT 244, 'ô', 'o'
UNION ALL
SELECT 245, 'õ', 'o'
UNION ALL
SELECT 246, 'ö', 'o'
UNION ALL
SELECT 247, '÷', '' -- ?
UNION ALL
SELECT 248, 'ø', '' -- ?
UNION ALL
SELECT 249, 'ù', 'u'
UNION ALL
SELECT 250, 'ú', 'u'
UNION ALL
SELECT 251, 'û', 'u'
UNION ALL
SELECT 252, 'ü', 'u'
UNION ALL
SELECT 253, 'ý', 'y'
UNION ALL
SELECT 254, 'þ', '' -- ?
GO
--DROP FUNCTION dbo.FixChars
GO
CREATE FUNCTION dbo.FixChars
(
@s-2 varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @newString varchar(8000)
, @C varchar(1)
, @newChar varchar(2)
, @pos int
SET @pos = 1
WHILE @pos <= Len(@s)
BEGIN
SET @C = Substring(@s, @pos, 1)
IF Ascii(@c) > 127
BEGIN
SELECT @newChar = Coalesce(m.newChar, @C)
FROM CharMap m
WHERE Ascii(@c) = m.prevAscii
SET @s-2 = Stuff(@s, @pos, 1, @newChar)
SET @pos = @pos + Len(@newChar)
END -- IF
ELSE
SET @pos = @pos + 1
END --WHILE
RETURN @s-2
END
GO
-------------------------------------------------------------
-- Test data
-------------------------------------------------------------
DECLARE @test-2 TABLE (data varchar(100))
INSERT @test-2
SELECT 'This is á TÈŠT striÑg'
UNION ALL
SELECT 'This is ánother TÈŠT striÑg'
UNION ALL
SELECT 'This is still á TÈŠT ŠtriÑg'
SELECT data FROM @test-2
SELECT dbo.FixChars(data) FROM @test-2
UPDATE @test-2 SET data = dbo.FixChars(data)
SELECT data FROM @test-2
-
Thanks, that helps! Also, to type the character, we can use ALT plue 0 plus the code
-
We have that issue with information pasted into our web pages and then that information needs to be copied, (and altered) into our SPs.
We created a table and function to handle this. Basically you can use the function on the string passed in - you may need to increase the length from 2000 to something larger...
Here is the table with some data - I would highly suggest using the ASCII characterset and including Descriptions!
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.SpecialCharacters') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE dbo.SpecialCharacters
GO
CREATE TABLE dbo.SpecialCharacters(
RowID integer IDENTITY (1, 1) NOT NULL,
SearchForCharacter varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
ReplacementCharacter varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Explanation varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [cuidx_SearchForCharacter] ON dbo.SpecialCharacters( SearchForCharacter) ON [PRIMARY]
GO
CREATE INDEX [idx_RowID] ON dbo.SpecialCharacters( RowID) ON [PRIMARY]
GO
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(8), CHAR(32), 'Replace a Backspace with a Space'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(9), CHAR(32), 'Replace a Horizontal Tab with a Space'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(10), CHAR(32), 'Replace a Line Feed with an Space'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(11), CHAR(32), 'Replace a Vertical Tab with a Space'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(12), CHAR(32), 'Replace a Form Feed with an Space'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(13), CHAR(32), 'Replace a Carriage Return with an Space'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(27), CHAR(32), 'Replace an Escape Character with an Space'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(96), CHAR(39), 'Replaces CHAR(96) [ ` ] with a Single Quote'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(145), CHAR(39), 'Replaces CHAR(145) [ ‘ ] with a Single Quote'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(146), CHAR(39), 'Replaces CHAR(146) [ ’ ] with a Single Quote'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(180), CHAR(44), 'Replaces an Odd Comma CHAR(180) [ ´ ] with the Standard Comma'
INSERT INTO SpecialCharacters( SearchForCharacter, ReplacementCharacter, Explanation)
SELECT CHAR(184), CHAR(44), 'Replaces an Odd Comma CHAR(184) [ ¸ ] with the Standard Comma'
Here is the funtion:
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id( N'dbo.ReplaceSpecialCharacter') AND xtype IN( N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.ReplaceSpecialCharacter
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION dbo.ReplaceSpecialCharacter( @ValueToAlter AS varchar(2000)) RETURNS varchar(2000)
AS
BEGIN
SELECT @ValueToAlter = REPLACE( @ValueToAlter, SearchForCharacter, ReplacementCharacter) FROM SpecialCharacters
RETURN ( @ValueToAlter )
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I wasn't born stupid - I had to study.
-
I'd like to personally applaud Farrell for getting off his training wheels and using a set based approach VS his old usual bulldozzer approach (even if he found this solution here) !
Congrats
-
That's quite the left-handed compliment... (Where did I find that here?)
I wasn't born stupid - I had to study.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply