SQL replace function problem

CraigLazar
CraigLazar used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select REPLACE(RTRIM(drvtotsize),' ','') AS ndrvTotalSize2
SELECT REPLACE('1 024 MB',' ','')

THIS WORKS
select REPLACE(LTRIM(RTRIM(drvtotsize)),' ','') AS ndrvTotalSize2

try this
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Author

Commented:
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?

Author

Commented:
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

Author

Commented:
thanks for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial