Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gracehanh
Starting Member
4 Posts |
 Posted - 2005-08-22 : 15:14:21
|
| Hi all,I have posted this message onto the General SQL but I thought under the Developer forum I might get luckier! ehehI am new to this forum and I have a question to ask you all.I am doing a BCP and I have written a Stored Procedure where I select two columns (column A and column B) from a table. In my select statement, I would like to determine whether the column is empty. If the column has data, I would like to write the data onto a csv file with a fixed CHAR(20) of size 20 (this means the data in my columns will never exceed 20 characters - the maximum number of characters is 20 and minimum is 1). If the data in the column has only 1 character it will still write the one character and the other 19 empty spaces into the csv file. For example column A returns "test12345" and column B returns "mytest123456". The result that on the csv file looks like this: 'test12345 ,mytest12345 ,'The empty spaces that I have right now will only work when there are at least one character.Now, if column A and column B is not, I would still like it to write the data to the csv file as: ' ,mytest123456 ,'Here is a part of my BCP command (without the CASE condition) in my Stored Procedure:DECLARE @sCommand NVARCHAR(4000)SET @sCommand = 'BCP "SELECT CAST(column_A AS CHAR(20)), CAST(column_B AS CHAR(20)) FROM ' + db_name() + '.dbo.myTable" queryout "C:\test.csv" -U sa -P admin -c -t ","'EXEC master..xp_cmdshell @sCommand, NO_OUTPUTI tried adding in CASE statement to determine when column_A or column_B is empty. If they are empty I still like to write the 20 empty spaces. But it didn't seem to work. If anyone has any ideas, please let me know.Thanks so much in advance.Grace |
|
|
gracehanh
Starting Member
4 Posts |
Posted - 2005-08-22 : 17:05:12
|
| Hello all,I have found a solution to my question above. All I needed to use was the SPACE function that is part of the String Functions in SQL. You can also use the STUFF function also.Grace |
 |
|
|
|
|
|
|
|
Write empty spaces onto csv file