format results of query..... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

format results of query.....

Author

Topic

Kristin
Starting Member

16 Posts

Posted - 2004-08-06 : 10:37:42

Does anyone know how to get rid of commas and spaces in the result set from my query??

Here is my SQL query:

SELECT records.result, company.company,
records.firstn + ' ' + records.lastn AS DONOR, records.donorid,
records.collecteddate, records.reason, records.lab,
records.speciminid,
records.amphetamine + ' ' + records.butalbital + ' ' + records.cocaine
+ ' ' + records.codeine + ' ' + records.heroin + ' ' + records.marijuana
+ ' ' + records.methadone + ' ' + records.methamphetamine + ' ' +
records.morphine + ' ' + records.phencyclidine + ' ' + records.propoxyphene
+ ' ' + records.otherdrug AS DRUGS, records.status,
records.reportcontact, records.reportdate, records.verifieddate,
records.signature, records.dot, records.nonregulated,
records.reportsentdate, records.form, records.recondate,
records.reconlab
FROM records INNER JOIN
company ON records.mrocomid = company.mrocomid
WHERE (records.recordnum = ?)

Obviously, "DRUGS" is the result set I am talking about....

But I need it to LOOK nice, and right now it comes out something like this....

" Methamphetamine Phencyclidine"

if I put commas in the query, it comes out looking like this:

,,,,,Methamphetamine,,,Phencyclidine,,

Need it to look like this:

Methamphetamine, Phencyclidine

or

Methamphetamine
Phencyclidine

Any ideas anyone???

Kristin

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-06 : 11:13:58

Where are you executing your query? If you want it to look nice pick up the results set and dump it into Excel or something.

If you're using query analyzer, consider Tools - options - Results and change the output format.

-------
Moo. :)

Kristin
Starting Member

16 Posts

Posted - 2004-08-06 : 15:34:02

Executing my query as a command in a Visual basic form....results shown on a datareport.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-06 : 15:38:10

Something along these lines??


DECLARE @Variable as VARCHAR(100)

SELECT @Variable = ',,,,,Methamphetamine,,,Phencyclidine,,'

SELECT REPLACE(@Variable, ',,', '')

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources