I am running a job in MS SQL Server that outputs a text file with white space in between columns. What I'd like to do is specify a specific character sequence between each column as a delimiter.
For example, I'd like the output to look like:
Apple%%%red%%%fruit
banana%%%yellow%%%fruit
onion%%%White%%%veggie
In this example, %%% is the delimiter.
How can I do this?
Assuming that you are using the output file of the job step, and the output file is currently structured something like this:
---- --------
row1 somedata
row2 somedata
You could just concatenate the columns using '+' and fit the percent signs in as appropriate. So the job step definition would contain:
select column1 + '%%%' + column2 from table1;
And the output would look like:
---------------
row1%%%somedata
row2%%%somedata
This assumes that you are OK with concatenating each row of results into a single column. You will need to cast/convert non-character column values for this to work.
My guess is that you are looking for the T-SQL equivalent of Oracle's P/SQL command "set colsep" command. This command lets you alter the delimter of the output. TO make it semicolon, for example, you would call:
set colsep ";"
But in SQL Server... I see the way to do it.
Use the "bcp" utility and you can specify the delimiter and write to your file. He are instructions:
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Look at the -t option to change the separator.
MySQLorSQL Server? They are not the same thing...MySQLassume this is an error? How are you generating this file at the moment?