0
votes

I am working on a project in which bulk data is being uploaded from a Excel file in my SQL table, this data include mostly the space characters which I am not able to replace by Ltrim Rtrim. I search for this special character Ascii, Sometimes it shows it ascii 160 sometimes 9. I also tried with Replace function but replaces the spaces between the string which is not my requirement.

Can any one help me out of this

1
Question is not clear - Dinidu Hewage
Try this, if this helps. improvingsoftware.com/2011/03/21/… - TechGirl
160 is nonbreaking space (html variant) and 9 is tab character. What problems causes replacing these characters with spaces and trimming afterwards? Or - could you replace these chars after inserting into SQL? - Arvo
Please include sample data and expected output. - Joseph B
How are you loading that data? - JotaBe

1 Answers

0
votes

Based on your question it is hard to know what you want the end result to be.

If you want to remove the ASCII 160 & 9, then it would be as simple as:

UPDATE tablename
   SET fieldname = REPLACE(REPLACE(fieldname, CHAR(160), ''), CHAR(9), '')

If you are wanting something else, please be specific in the question and provide a few samples of the data.