1

I have table where numbers are stored in nvarchar data type. The data looks as follows:

Numbers
---------
200
504 040
694 380
153 272
26 296

Now I'd like to get rid of the blank space in the numbers. However, REPLACE(Numbers,' ','') does not work as it seems the blank space is some kind of special "blank space".

So I tried to find any special character here using the function I found on SO (link). Nothing happened, not any special character was found.

So the next step was exporting the record into .csv file and check data's encoding using TotalCMD. The result can be seen here:

enter image description here

Any ideas what could go wrong and how to replace those blank spaces? I think the Unicode encoding shows the problem, but I am lacking of knowledge in this area so I also can be wrong.

Update #1 - to show you more

The table looks like this:

Category    Number
-------------------
Cars           200
Cars       504 040
Cars       694 380
Cars       153 272
Cars        26 296
Bikes       50 000
Bikes       21 412
Bikes        8 541

Now I run the query:

SELECT 
    t.Category, REPLACE(t.Numbers,' ','')
FROM 
    tableMix AS t
WHERE 
    t.Category = 'Cars'

SELECT 
    t.Category, REPLACE(t.Numbers,' ','')
FROM 
    tableMix AS t
WHERE 
    t.Category = 'Bikes'

What is strange - the first query does not replace blank spaces for the Cars category, however the second query works without any problem for number values for Bikes category.

Community
  • 1
  • 1
DNac
  • 2,663
  • 8
  • 31
  • 54
  • I think you are doing something wrong because replace must work here. Please post your query. – Ankit Bajpai Dec 11 '15 at 09:47
  • I don't think so as with other numbers I have no problem and I am doing it in the same way. The query is posted already, just without SELECT and FROM clause. I still update the question for you, sec. – DNac Dec 11 '15 at 09:50
  • 2
    Try answer from [this question](http://stackoverflow.com/questions/15214848/how-do-i-remove-non-breaking-spaces-from-a-column-in-sql-server) – EekTheCat Dec 11 '15 at 10:09
  • Thanks. That seems to be it - the NCHAR(160). – DNac Dec 11 '15 at 10:12

1 Answers1

0

Try to make number from the nvarchar like this

SELECT 
    t.Category, cast(t.Numbers as int)
FROM 
    tableMix AS t
WHERE 
    t.Category = 'Cars'
Marc
  • 6,051
  • 5
  • 26
  • 56
János Spengler
  • 109
  • 6
  • You are unfortunately not able to do it. Also, the problem was already resolved, see comments in the question. – DNac Dec 11 '15 at 10:56