Column space

Is there a command so that I can enter in my query to make a 1 space before
my results. The reason is that I'm currenlty running a report that gives me
patients demographics in a .CSV file and I used DTS to automate the process
the last information on each patient is SS# this file will then need to be
imported to to another vendor application. The problem is that the vendor
found a bug on their application that cuts the first digit on the SSN and in
order for them to fix their app. it will take months and I can't wait this
long. So what i'm trying to do is move the results on the last field (SSN#)
one space to the right so that the results on the .CSV file will be
,*111-11-1111
* blank space
Is this possible without manually editing the .CSV file?
Thanks,
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> Is there a command so that I can enter in my query to make a 1 space
> before
> my results. The reason is that I'm currenlty running a report that gives
> me
> patients demographics in a .CSV file and I used DTS to automate the
> process
> the last information on each patient is SS# this file will then need to be
> imported to to another vendor application. The problem is that the vendor
> found a bug on their application that cuts the first digit on the SSN and
> in
> order for them to fix their app. it will take months and I can't wait this
> long. So what i'm trying to do is move the results on the last field
> (SSN#)
> one space to the right so that the results on the .CSV file will be
> ,*111-11-1111
> * blank space
> Is this possible without manually editing the .CSV file?
> Thanks,
There are a couple of ways of handling this.
1. Does it have to be a space? I would suggest some other identifying
mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
won't erase that space.
2. In the query itself, you can do something like this:
SELECT col1, col2, '*' + SSNColumn
FROM tablename
3. If that is undesirable, then you can always create a view with the above
SELECT statement and then run DTS against the view rather than the base
table.
Rick Sawtell
MCT, MCSD, MCDBA
|||So if I want to try this without the asteriks (*) or a tilde(~). I would
type my query as: SELECT col1, col2, + SSNColumn
FROM tablename
Or: SELECT col1, col2, '' + SSNColumn
FROM tablename
"Rick Sawtell" wrote:

> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> There are a couple of ways of handling this.
> 1. Does it have to be a space? I would suggest some other identifying
> mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
> won't erase that space.
> 2. In the query itself, you can do something like this:
> SELECT col1, col2, '*' + SSNColumn
> FROM tablename
> 3. If that is undesirable, then you can always create a view with the above
> SELECT statement and then run DTS against the view rather than the base
> table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
|||Thanks Rick Sawtell,
The Second option worked perfectly.
SELECT col1, col2, ' ' + SSNColumn
FROM tablename
"Carlos Santos" wrote:
[vbcol=seagreen]
> So if I want to try this without the asteriks (*) or a tilde(~). I would
> type my query as: SELECT col1, col2, + SSNColumn
> FROM tablename
> Or: SELECT col1, col2, '' + SSNColumn
> FROM tablename
>
>
> "Rick Sawtell" wrote:
|||Carlos Santos wrote:
> Thanks Rick Sawtell,
> The Second option worked perfectly.
> SELECT col1, col2, ' ' + SSNColumn
> FROM tablename
> "Carlos Santos" wrote:
>
You may want to use SPACE(1) instead so it's clear in the proc what's
prefixing the string. You could easily add a second space and may not
realize it from the code. Some performance testing may be in order, but
I wouldn't expect that using the SPACE function would incurr much
overhead.
David Gugick
Imceda Software
www.imceda.com