How do I replace a value in a string?

[sbroomfield]

Hi,

I have some SQL that I run to create output to a csv file.
This is mailed out to my users and when they open the file it opens in Excel.
This is all very good, but two of the columns contain strings that may include commas at any point in the string. This obviously buggers up the output!
I have looked at the possibility of amending those fields on the database but there are 3000 instances of these strings containing commas so I had better think about manipulating the data once I have retrieved it from the database.
Is there a way that I can specify that if there is a comma anywhere in the string, replace it with a space?

Hope someone can help,

Thanks,

Steve

 

[qyllr]

hello,

it's really easy in perl or sed in unix...

you can run a perl script like this...

#####################################

#! /usr/local/bin/perl

open (INFILE,"$inFile&quot
or die "Error: Cannot Open File $inFile: $!\n";
open (OUTFILE,">$outFile&quot
or die "Error: Cannot Open File $outFile: $!\n";

while (<INFILE>)
{
$row = $_;
$row =~ s/^\s+//g;
$row =~ s/\s+$//g;
$row =~ s/,/ /g;
print OUTFILE &quot;$row\n&quot;;
}

close (INFILE);
close (OUTFILE);

#####################################

where $inFile is the output file generated by your SQL...
and $outFile is the new output file...


hth,
q.

 

[JMCraig]

The Perl solution is slick, but you can do it in T-SQL if that's simpler for you. I was starting to put in loop that searches in a string (the problem data) and replaces commas with blanks, but how about just surrounding the columns in question with quotes and using say TAB delimiters?

select numeric_col + char (9)
+ '&quot;' + text_col + '&quot;' + char (9)
...

That might be easier.

If you're interested in doing the comma to blank replacement in T-SQL, I'll post a way to do that. If you wrap a loop in a stored procedure it's not so bad to deal with it that way--although it sure would be slick to be able to do things like that in line. Java in the DB anyone?

BOL,

J M Craig
Alpha-G Consulting, LLC

http://alphagconsulting.bizhosting.com

 

[qyllr]

Hi Qyllr,

Is there a Perl module for Sybase that I can use? By the way your perl script works fine.

Thanks man,
Royler

 

[qyllr]

hello,

yes, there is a module called sybperl that you can download...

how did you access my account? do i know you?

mike

 

[royler]

Hi Qyllr,

I have been having problems with my account. When I replied to your posting, it showed up as your posting. I already contacted the SysAdmin. Thanks for the reply.

Royler

 

[stevenippon]

don't know if this helps, but another way to do this is extract data with another separator ( e.g. use -s&quot;~&quot; in sql command). Then load into Excel using this as separator. ( we have to do this as we need to keep comma's in the data but suppose it's not important for you)