SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Manual Data Entered - REPLACE command help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indrajit_wsr
Starting Member

2 Posts
Posted - 10/08/2012 :  06:11:08  Show Profile  Reply with Quote
Hello,
We have an SQL server system where in we have a data that seems to be manually entered and have got a weird character at its end not sure if its carriage return or tab as below

J->
where -> is that junk character I m seeing in data

select REPLACE(REPLACE('J->', CHAR(10), ''), CHAR(13), '')

I need to replace this -> with spaces. The data type of this field is char(10) and for this particular record it appears to be on 2nd character.

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/08/2012 :  06:59:37  Show Profile  Reply with Quote
If you do want to replace the CHAR(10) and char(13) with spaces or blanks, what you are doing is correct. But I suspect the characters are something other than those two. You can find what those are using
SELECT CAST('J->' AS VARBINARY)
When I do that with the string you posted, I get this:
0x4A2D3E1A
Now, you can replace any of the four characters that you want to - for example, to replace the second character:
REPLACE('J->',CHAR(0x2D),'')
Edited by - sunitabeck on 10/08/2012 07:00:01
Go to Top of Page

indrajit_wsr
Starting Member

2 Posts
Posted - 10/08/2012 :  08:05:45  Show Profile  Reply with Quote
Thank You..!!!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 10/08/2012 :  08:21:47  Show Profile  Reply with Quote
Glad to help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000