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
Showing posts with label currenlty. Show all posts
Showing posts with label currenlty. Show all posts
Wednesday, March 7, 2012
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...
> > 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
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
Subscribe to:
Posts (Atom)