SQLTeam.com | Weblogs | Forums

Reserved blank CHAR(#) for empty spacing

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

1 Like

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

1 Like

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?

1 Like

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

                  8888

I 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?

1 Like

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.

1 Like

:eyes: :eyes: :eyes: wow explains a lot of things :grinning_face_with_smiling_eyes:

1 Like

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.