Hi,
What is the best approach to have the space for specific CHAR I want on a column in SQL without creating a table?
Here is a basic example:
The above query of
SELECT LEN(CONVERT(CHAR(9),' ')) AS SPACING
,LEN('8888') AS TEXT
,LEN(CONVERT(CHAR(9),' ')) AS SPACING
Returns 0 LEN on the first and third columns. I am expecting to see 9 instead.
How do I reserve the space (in this example 9 characters) for empty space?
What are you attempting to solve? End goal
To get the exact position on every columns when output to flat file output, Yosiasz.
So you want the 9 char space as a delimiter of each column?
only if the column is empty and has no string at all, Yosiasz.In this example would be column 1 and 3.
Look at STUFF finctiom as an option
Thank you Yosiasz
You can use the SPACE function to insert a number of spaces - or REPLICATE. The problem you are seeing is because LEN does not count trailing spaces. If you want to see the actual data length - use the function DATALENGTH.
To build a flat file record - you can do something like:
Select concat('x', space(9), 'x');
With that said - how are you outputting this data to a file?
Hi Jeffw8713,
You are correct, I can see the LENGTH of o if I use the DATALENGTHfunction,
SELECT DATALENGTH(CONVERT(CHAR(9),' ')) AS SPACING
What I am trying to achieve is to output the SQL statement to flat file with fixed position.
Position 1-9 (blank) Position 10 - 13 (Text) Position 14 - 22 (blank)
Ex: I am using the X to represent the empty spacing for example.
XXXXXXXXX8888XXXXXXXXX
So the output I want should be like below
8888I understand what you are doing - I was asking about the tool you are using to actually create the file. If you use SSIS then all of that is not necessary and will be handled by SSIS.
If you are using BCP or something else - then that is the easiest way to get it done.
If you look at my example using CONCAT - it does exactly what you want. However - you should only pad spaces where you are dealing with filler fields. For actual data fields then you want to convert the data to a fixed CHAR size.
For example - if the first field is 10 characters and you only have a length of 4 and it needs to be right-aligned:
Select concat(right(concat(space(10), 8888), 10), space(2), cast('AAA' As char(5)), cast('BBB' As char(8)), 'xxx');
For ease of maintenance - I would recommend documenting the code and structuring it:
Select concat(right(concat(space(10), 8888), 10) /* Field 1 position 01-10 - Account# */
, space(2) /* Field 2 position 11-12 - Filler */
, cast('AAA' As char(5)) /* Field 3 position 13-18 - Group */
, cast('BBB' As char(8)) /* Field 4 position 19-27 - SubGroup */
, 'xxx' /* Field 5 position 28-31 - End */
) /* end concat */
From ...
Where ...;
what upstream process consumes this flat file? and why use fixed position when you can use tab delimited or some other delimiter?
Believe it or not - CMS (cms.gov) requires files to be submitted as fixed-width flat files with a header record, detail records and a trailer record. There are others that also require fixed-width files for submission.
I had to create an SSIS project to load files sent by CMS - all of them were fixed-width flat files.
Still in use in many industries.
wow explains a lot of things 
Yeah, the car dealership accounting team vendor preferred the fixed output.
As a bit of a sidebar, stop using the TEXT datatype. It's been deprecated since 2005. Use VARCHAR(MAX) instead. Also, when you do, consider setting the "large object out of row" option for the table because the MAX datatypes default to "In-row" and will make a mess of your clustered index when short rows are present.
I don't see that the OP is using the TEXT data type. I do see that the example posted named a column 'TEXT' but that is not the same.
Still good advice - just not sure where you see the usage of the text data type.
Ah... you're correct. I looked at it too quickly and mistook it as a datatype. Thanks Jeff.
Thank you Jeff