asked on

SQL replace function problem

Hi i am battling with the replace statement. I am using it to remove the spaces in the following string value 1 024 MB. It is only removing the 2nd space to 1 024MB.
Is there another method i could try?

REPLACE(drvtotsize,' ','') AS ndrvTotalSize2,

thanks

SQL_SERVER_DBA

select REPLACE(RTRIM(drvtotsize),' ','') AS ndrvTotalSize2

ASKER CERTIFIED SOLUTION

SQL_SERVER_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

shru_0409

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CraigLazar

ASKER

Hi, ok thanks for the input, i have tried each method and it is still there, the field type is a varchar(50).
I have another process which is writing the values into this field from a xml file. so even i copy the field value from the table in to the following statement
SELECT REPLACE('1 016 MB',' ','')
it still does not remove the first space, is it possible that it could be another character type other than a SPACE?

CraigLazar

ASKER

hi guys,
ok i have found the problem, the first "space" value is actually not a space character its something else. Now i am not sure how to find out what ASCII character possibly could be, but i took the string as it was out the table, copied the character and put that into the second parameter of the replace statement and it worked.

thanks for the help

cheers

CraigLazar

ASKER

thanks for the help