How to replace these special characters?

Question

How to replace these special characters?

Jobs90312

Mr or Mrs. 500

Points: 533
More actions
May 17, 2023 at 11:39 am
Go to Answer
#4191538

Hi everyone,
I have a SQL table in which there is a name column. I found these 3 names which contains "single quote" but all 3 are different types of "single quotes".
I am using replace function to remove this quoute but its only removing it from the last name.
How can I remove it from all 3 names?
Mus’ab
‘ABD ALRAHMAN
SA'D
Phil Parkin

SSC Guru

Points: 246960
More actions
May 17, 2023 at 11:42 am
Answer
#4191539
They are different characters and therefore multiple nested REPLACE commands would be required.
```
DECLARE @x VARCHAR(50) = CONCAT ('Some''', CHAR (96), CHAR (145), CHAR (146));

SELECT @x
      ,REPLACE (REPLACE (REPLACE (REPLACE (@x, '''', ''), CHAR (96), ''), CHAR (145), ''), CHAR (146), '');
```
- This reply was modified 1 year, 6 months ago by Phil Parkin.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jobs90312 Mr or Mrs. 500 Points: 533 More actions · Answer 1

Phil Parkin wrote:

They are different characters and therefore multiple nested REPLACE commands would be required.
DECLARE @x VARCHAR(50) = CONCAT ('Some''', CHAR (96), CHAR (145), CHAR (146));

SELECT @x
      ,REPLACE (REPLACE (REPLACE (REPLACE (@x, '''', ''), CHAR (96), ''), CHAR (145), ''), CHAR (146), '');

Thank you very much sir.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

To figure out which CHAR s are in the string ... check this thread https://www.sqlservercentral.com/forums/topic/how-to-remove-special-chars#post-4157818

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

but this leads to the question - why do you need to remove them? they are valid characters and if the output is being displayed anywhere for users removing those is a big issue as you are CHANGING what the name of the individuals is.

what should be done if nothing else is replacing the "smart quote", normally used by Word and similar word processing applications with the correct apostrophe.

This reply was modified 1 year, 6 months ago by frederico_fonseca.

Jobs90312 Mr or Mrs. 500 Points: 533 More actions · Answer 4

frederico_fonseca wrote:

but this leads to the question - why do you need to remove them? they are valid characters and if the output is being displayed anywhere for users removing those is a big issue as you are CHANGING what the name of the individuals is.
what should be done if nothing else is replacing the "smart quote", normally used by Word and similar word processing applications with the correct apostrophe.

Requirement of client. They asked to deliver it in this format. It is their problem if name of individual is changed. We just have to apply these changes.