February 6, 2009 at 11:16 pm
hi,
i had run
1) INSERT INTO test VALUES('sandy')
2) INSERT INTO test VALUES('sandy
')
3) INSERT INTO test VALUES('san
dy')
Can you please tell me how to retrieve only 'sandy' in all the threecases?
as when i export data in excel using bcp command in
2nd case new blank row get inserted
3rd case san comes to one row and dy comes in next row
February 7, 2009 at 4:17 am
You can use the queryout option in BCP which lets you export data according to a query instead of exporting a full table. In your query you have to check that you don’t have an enter in the string. The enter is created from 2 ASCII codes – 10 and 13, so in the query you just have to make sure that they don’t exist in your string. You can do that with charindex query. If your column name is VC, then the query might look like this:
select * from test where charindex(char(13)+char(10), vc) = 0
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2009 at 4:52 am
Adi, you were right on how to filter the rows that contain newline or carriage return characters in a string. But the OP wanted to eliminate those characters from the columns. So, I think he needs to use REPLACE, in this case.
SELECT REPLACE( VC, CHAR(10), REPLACE( VC, CHAR(13), '' ), '' ) FROM test
--Ramesh
February 8, 2009 at 3:18 am
Yep, that was wrong. Thank you for the correction.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 8, 2009 at 9:57 pm
hi Adi,
when i run your code
select * from test where charindex(char(13)+char(10), vc) = 0
it gives me only one record in output but wherein it should be three and
When i had ramesh code
SELECT REPLACE( VC, CHAR(10), REPLACE( VC, CHAR(13), '' ), '' ) FROM test
it gives me an error :Server: Msg 174, Level 15, State 1, Line 1
The replace function requires 3 arguments.
so when i modified the code to:
SELECT REPLACE(REPLACE( VC, CHAR(13), '' ),char(10),'') FROM test
it given me the exact three records.
So can you please correct me if i am wrong or i can go with this code.
February 9, 2009 at 1:13 pm
Sorry for the late response. You can use only one replace function and in that function replace char(13) + char(10) with an empty string:
SELECT replace(vc, char(13) + char(10), '') from test
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply