Unseen characters

Question

Post reply

Unseen characters

WonderPra

SSCommitted

Points: 1624
More actions
February 15, 2013 at 5:29 am

#270228

I have a table dbo.address in which i have an address column. When i am trying to do the len function on that column its giving me wrong count.. For example a field with 760 jenness should give me an output of 12 instead of 11.. There is some bad hidden character which i am unable to figure out. I tried using ltrim n rteim but of no use.. My table is a pretty big one and i am supposed to update the entire column and remove those hiddn bad characters.. Any suggestions??? 🙂
--Pra:-):-)--------------------------------------------------------------------------------

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

It'll potentially take some time: -

UPDATE yourTable

SET yourColumn = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

yourColumn COLLATE Latin1_General_BIN,

CHAR(0), '') COLLATE Latin1_General_BIN,

CHAR(1), '') COLLATE Latin1_General_BIN,

CHAR(2), '') COLLATE Latin1_General_BIN,

CHAR(3), '') COLLATE Latin1_General_BIN,

CHAR(4), '') COLLATE Latin1_General_BIN,

CHAR(5), '') COLLATE Latin1_General_BIN,

CHAR(6), '') COLLATE Latin1_General_BIN,

CHAR(7), '') COLLATE Latin1_General_BIN,

CHAR(8), '') COLLATE Latin1_General_BIN,

CHAR(9), '') COLLATE Latin1_General_BIN,

CHAR(10), '') COLLATE Latin1_General_BIN,

CHAR(11), '') COLLATE Latin1_General_BIN,

CHAR(12), '') COLLATE Latin1_General_BIN,

CHAR(13), '') COLLATE Latin1_General_BIN,

CHAR(14), '') COLLATE Latin1_General_BIN,

CHAR(15), '') COLLATE Latin1_General_BIN,

CHAR(16), '') COLLATE Latin1_General_BIN,

CHAR(17), '') COLLATE Latin1_General_BIN,

CHAR(18), '') COLLATE Latin1_General_BIN,

CHAR(19), '') COLLATE Latin1_General_BIN,

CHAR(20), '') COLLATE Latin1_General_BIN,

CHAR(21), '') COLLATE Latin1_General_BIN,

CHAR(22), '') COLLATE Latin1_General_BIN,

CHAR(23), '') COLLATE Latin1_General_BIN,

CHAR(24), '') COLLATE Latin1_General_BIN,

CHAR(25), '') COLLATE Latin1_General_BIN,

CHAR(26), '') COLLATE Latin1_General_BIN,

CHAR(27), '') COLLATE Latin1_General_BIN,

CHAR(28), '') COLLATE Latin1_General_BIN,

CHAR(29), '') COLLATE Latin1_General_BIN,

CHAR(30), '') COLLATE Latin1_General_BIN,

CHAR(31), '') COLLATE Latin1_General_BIN,

CHAR(127), '') COLLATE Latin1_General_BIN;

Personally, I'd back up the column somewhere first - maybe a look-up table that contains the PKey, the column and a computed column that uses the replace string I've listed above?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 2

take a look at this thread; it can help you find the ascii code of the unseen character(s)

;

from there, you can decide whether you want to replace it or whatever:

there's several ideas in the thread for cleaning up fields.

http://www.sqlservercentral.com/Forums/Topic1408071-391-1.aspx

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!

WonderPra SSCommitted Points: 1624 More actions · Answer 3

Lowell,

Thankyou for ur response.. It worked so well.. I just couldnt believe that my problem resolves in no tym... 🙂 Would you also help me Now if i want to delete those particular positions with the ASCII values, what is the best move..? i just thought to dump all the data into a temp table and delete those rows with the ASCII (9 values ) but that just deletes from the temp table. But I want that to be applied to my main table...

--Pra:-):-)--------------------------------------------------------------------------------

Lowell SSC Guru Points: 323518 More actions · Answer 4

prathibha_aviator (2/15/2013)
Lowell,
Thankyou for ur response.. It worked so well.. I just couldnt believe that my problem resolves in no tym... 🙂 Would you also help me Now if i want to delete those particular positions with the ASCII values, what is the best move..? i just thought to dump all the data into a temp table and delete those rows with the ASCII (9 values ) but that just deletes from the temp table. But I want that to be applied to my main table...

well, i'd hope that we could see some examples so we could really be sure;

are you using the StripNonAlphaNumeric function?

but you could do something as simple as this:

DELETE FROM SOMETABLE

WHERE dbo.StripNonAlphaNumeric(SomeColumn) = '';

or some other test like SomeColumn LIKE '%' + CHAR(9) + '%', as long as you can identify the offending chars in a WHERE statement.

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!

WonderPra SSCommitted Points: 1624 More actions · Answer 5

Thankyou Again:)

--Pra:-):-)--------------------------------------------------------------------------------

WonderPra SSCommitted Points: 1624 More actions · Answer 6

select
h_Address_1,
N as Position,
DioUniqueID,
LogDate,
SUBSTRING(h_Address_1,N,1) As TheChar,
ASCII(SUBSTRING(h_Address_1,N,1)) TheAsciiCode
from LOG_tblAddressBK
CROSS APPLY (SELECT TOP 255 ROW_NUMBER() OVER (ORDER BY (SELECT NULL) -1) AS n
FROM sys.columns) MiniTally
WHERE MiniTally.n BETWEEN 0 AND 255
AND MiniTally.n < LEN(h_Address_1) +1
AND DioUniqueID = 207261
--AND LogDate = '2013-02-14 15:40:04.720'
ORDER BY h_Address_1

are you using the StripNonAlphaNumeric function?

Not yet, I have never used such a function before.. I will read upon that now.. thanks though... You saved my day

--Pra:-):-)--------------------------------------------------------------------------------

WonderPra SSCommitted Points: 1624 More actions · Answer 7

Ahhh This works.... and you are a fantastic listener.. Thanks a ton

select * from LOG_tblAddtest
WHERE h1_Add LIKE '%' + CHAR(9) + '%'
AND DioUniID = 207261;

--Pra:-):-)--------------------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

prathibha_aviator (2/15/2013)
For example a field with 760 jenness should give me an output of 12 instead of 11..

Perhaps it's just a bit of phat phingering but "760 jenness" only has 11 characters in it. Why would you expect "12" to be the answer?

760 jenness

12345678901

--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)

WonderPra SSCommitted Points: 1624 More actions · Answer 9

M sorry i mean 11 instead of 12... Sry for the confusion

--Pra:-):-)--------------------------------------------------------------------------------