How to remove diamond character from string in sql

Question

How to remove diamond character from string in sql

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
October 20, 2022 at 2:24 pm

#4101996

I have a column with data where i am getting character �
Example - data looks like Student�
Tried to use replace function, didnt worked, Any function to replace this?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 1

If you are unfamiliar with the ascii character set, you will need to look that up.

The hammer would be to process each character in the string with ASCII() and only copy those characters that come back in a specific range to a new string. When you are done copy your new string back over your old string. Continue ...

Not sure there is an elegant way that doesn't come down to what I suggest.

This reply was modified 2 years, 1 month ago by Tobar.

<><
Livin' down on the cube farm. Left, left, then a right.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

What is the datatype of the column that symbol appears in?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed B Hall of Fame Points: 3665 More actions · Answer 3

Does this help? If there are other characters you may be able to identify their NCHAR value with UNICODE.

DECLARE @Column NVARCHAR(100) 
SET     @Column = CONCAT('Student' COLLATE Latin1_General_BIN , NCHAR(65533))
SELECT  @Column,
        UNICODE(RIGHT(@Column, 1)),
        REPLACE(@Column,  NCHAR(65533), N'' COLLATE Latin1_General_BIN)

ratbak Ten Centuries Points: 1358 More actions · Answer 4

If it's varchar and not nvarchar...

That's probably not the actual character -- rather what SSMS is translating it to for display.

You can use a tally table or tally function (see Jeff Moden's links) to cross apply the column to get the position & ascii code of the offending character(s) -- e.g.,

DECLARE @string VARCHAR(255) = (SELECT StringColumn FROM dbo.MyTable WHERE ID = 7);

SELECT Number,SUBSTRING(@string,Number,1),ASCII(SUBSTRING(@string,Number,1))
FROM util.Tally(0,LEN(@string)) numbers
WHERE Number > 0

-- Or if table is small or you can add a filter to limit to offending rows):
SELECT Number,SUBSTRING(StringColumn,Number,1),ASCII(SUBSTRING(StringColumn,Number,1))
FROM dbo.MyTable
CROSS APPLY util.Tally(0,LEN(StringColumn )) numbers
WHERE Number > 0

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

@mcfarlandparkway

Asking one more time...

Jeff Moden wrote:

What is the datatype of the column that symbol appears in?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 6

mcfarlandparkway

SSCertifiable

Points: 7699

October 21, 2022 at 4:21 pm

#4102591

Its nvarchar(50)

ratbak Ten Centuries Points: 1358 More actions · Answer 7

What is the collation?

Does it look that way in SSMS (or another application/client?)

If SSMS, check this article for how to set grid to display unicode characters. It suggests Arial Unicode MS. If you want a fixed-width font, search engine should provide some options.

If application, does the app support unicode?

(Just trying to determine if replace is really necessary, or if this is just a display issue -- the fact the column is defined as nvarchar implies you want to support unicode)

ratbak Ten Centuries Points: 1358 More actions · Answer 8

What is the actual character? What does UNICODE(RIGHT(ColumnName, 1)) return?

That diamond character is the unicode Specials replacement character U+FFFD:

If that actually is the value stored, then it's likely the collation doesn't support the "real" character or application/process that inserted it didn't.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

ratbak wrote:

What is the actual character? What does UNICODE(RIGHT(ColumnName, 1)) return? That diamond character is the unicode Specials replacement character U+FFFD: If that actually is the value stored, then it's likely the collation doesn't support the "real" character or application/process that inserted it didn't.

Spot on and that's why I was asking what the datatype of the column is.

@mcfarlandparkway ...

It would have helped your cause if you posted an example of your REPLACE() code... like this... which clearly demonstrates the problem...

--===== This doesn't work
 SELECT Test = REPLACE(N'Student�',N'�',N'')
;

Test
-------------
Student�

Then, someone could have come along with a clear demonstration of a solution... like this... without several posts of just suggestions (which are still great IF you understand the suggestions)...

--===== Force a "common collation" on all the parts to do the match
 SELECT ThisWorks = REPLACE(N'Student�' COLLATE Latin1_General_BIN2,N'�',N'') 
;

Results:
ThisWorks
------------
Student

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

At McFarlandParkway,

Are you all set? Did the solution I posted work for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)