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...
> > 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
>
>
>|||Thanks Rick Sawtell,
The Second option worked perfectly.
SELECT col1, col2, ' ' + SSNColumn
FROM tablename
"Carlos Santos" wrote:
> 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...
> > > 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
> >
> >
> >
> >
> >|||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
