How to replace characters that don't belong to the collation

Question

Post reply

How to replace characters that don't belong to the collation

river1

SSC-Insane

Points: 24309
More actions
May 28, 2014 at 4:31 pm

#289308

Hi,
I have some views.
When I select this views I want to replace the character "|" by "" and any character that is inserted into the database that does not belong to the collation.
To replace "|" on the result set off the view I can do like:
Select replace(fileds1,"|",""),field2......
But how can I replace the invalid charaters (characters that don't belong to the collation)
on the result set of the view?
Thanks,
Pedro

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

The CHAR()/NCHAR() functions can be used as a parameter for the REPLACE function

😎

SELECT REPLACE('STUFF TO |REPLACE',CHAR(124),'')

Use either the ASCII() or UNICODE() to find the character code

SELECT UNICODE(N'|')

SELECT ASCII('|')

This code lists the first 233 printable characters

;WITH TN(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMBERS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM TN T1,TN T2,TN T3)

SELECT

N AS CHAR_CODE

,NCHAR(N) AS CHAR_ACTER

FROM NUMBERS NM

WHERE NM.N BETWEEN 32 AND 255

river1 SSC-Insane Points: 24309 More actions · Answer 2

I'm sorry. I didn't undesrtood your answer. Can you explain it other way?

Thanks

river1 SSC-Insane Points: 24309 More actions · Answer 3

For instance:

I have replaced the character "|" on the view (in column DESC_ACTIVIDADE):

ALTER view [dbo].[vwSGCT_TO_CENTRAL_ACTIVIDADES]

as

select

ac.codctb,a.rgc as rgc,

dbo.spSGCT_NIFCTB_TO_NIF_CENTRAL(a.nifctb) as NIF,

dbo.spSGCT_NIFCTB_TO_FILIAL_NUMBER_CENTRAL(a.nifctb) as FILIAL_NUMBER,

INDEX_POS,

replace(DESC_ACTIVIDADE,'|','')DESC_ACTIVIDADE

from

actividadesac join

allctb aon ac.codctb = a.codctb join

vwGetCtbsRFvw on a.codctb = vw.codctb

How can I say to this view for the same column (DESC_ACTIVIDADE) that I want to replace characters that don't belong to the collation by ''

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I don't know whether this'll work, but it's worth a try. Do it on a test server, not in production. Find a row in the table that contains one of the illegal characters. Suppose the character is "¬", then your row might look something like this:

[font="Courier New"]ID Text

-- ----

11 The quick¬ brown fox[/font]

Isolate the character like this:

UPDATE MyTable

-- Not sure whether I've got the SUBSTRING syntax correct, but

-- what you're trying to do is get the 10th character only

SET Text = SUBSTRING(Text, 10, 1)

WHERE ID = 11

Then get the ASCII code like this:

SELECT ASCII(Text)

FROM MyTable

WHERE ID = 11

Once you know what the ASCII code is, that'll help you implement what Eirikur suggested.

John

river1 SSC-Insane Points: 24309 More actions · Answer 5

I think that It is very dificult to implement on aview...

A view like:

select name, address from teste

I can replace the "|" character by:

select replace(name,"|",""), replace(address,"|") from teste

I would like to replace the invalid character by nothing.

Can you do anything in the select statment of the view to prevent the result set from sending this character as part of the result?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

I only mean that you should do that as a one-off, in order to discover the ASCII number. Once you've discovered it (let's say it's 99), you can do something like this:

select replace(name,CHAR(99),''), replace(address,CHAR(99),'') from teste

Of course, if it turns out that the ASCII code is NULL for a character that doesn't appear in a character set, you'll need to do something more elaborate with string splitters and number tables and so on.

John