format results of query

Question

Post reply

format results of query

Kristin Stariha

Ten Centuries

Points: 1012
More actions
August 6, 2004 at 8:41 am

#360534

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
Kristin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Shaji Venugopal Old Hand Points: 348 More actions · Answer 1

you could try

isnull(rtrim(records.amphetamine), ' ')

I think this should work.

regards

Shaji

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 2

hmmmm, the results still just all run together like:

MethamphetaminePhencyclidineOpiates

here is what I did...

SELECT records.result, company.company,

records.firstn + ' ' + records.lastn AS DONOR, records.donorid,

records.collecteddate, records.reason, records.lab,

records.speciminid, ISNULL(RTRIM(records.amphetamine), ' ')

+ ISNULL(RTRIM(records.butalbital), ' ')

+ ISNULL(RTRIM(records.cocaine), ' ')

+ ISNULL(RTRIM(records.codeine), ' ')

+ ISNULL(RTRIM(records.heroin), ' ')

+ ISNULL(RTRIM(records.marijuana), ' ')

+ ISNULL(RTRIM(records.methadone), ' ')

+ ISNULL(RTRIM(records.methamphetamine), ' ')

+ ISNULL(RTRIM(records.morphine), ' ')

+ ISNULL(RTRIM(records.phencyclidine), ' ')

+ ISNULL(RTRIM(records.propoxyphene), ' ')

+ ISNULL(RTRIM(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, records.verifysig

FROM records INNER JOIN

company ON records.mrocomid = company.mrocomid

WHERE (records.recordnum = ?)

whaddya think??

kristin

Kristin

Bill Nye Hall of Fame Points: 3248 More actions · Answer 3

There's probably a neater way, but this works:

replace(replace(isnull([c1],'')+' '+ isnull([c2],'')+' '+ isnull([c3],'')+' '+ isnull([c4],'')+' '+ isnull([c5],''),' ',' '),' ',',')

The first replace gets rid of the two spaces where there is an empty string and the second replaces the remaining paces with commas.

Bill

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 4

tells me isnull requires two arguments????

what does that mean?

Kristin

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 5

ok, here is my query and the results I get????

SELECT records.result, company.company,

records.firstn + ' ' + records.lastn AS DONOR, records.donorid,

records.collecteddate, records.reason, records.lab,

records.speciminid,

REPLACE(REPLACE(ISNULL(records.amphetamine, '')

+ ' ' + ISNULL(records.butalbital, '')

+ ' ' + ISNULL(records.cocaine, '')

+ ' ' + ISNULL(records.codeine, '')

+ ' ' + ISNULL(records.heroin, '')

+ ' ' + ISNULL(records.marijuana, '')

+ ' ' + ISNULL(records.methadone, '')

+ ' ' + ISNULL(records.methamphetamine, '')

+ ' ' + ISNULL(records.morphine, '')

+ ' ' + ISNULL(records.phencyclidine, '')

+ ' ' + ISNULL(records.propoxyphene, '')

+ ' ' + ISNULL(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, records.verifysig

FROM records INNER JOIN

company ON records.mrocomid = company.mrocomid

WHERE (records.recordnum = ?)

results I get are:

,,,Methadone,Morphine,,Opiates

hmmmm

Kristin

Bill Nye Hall of Fame Points: 3248 More actions · Answer 6

Sorry. I didn't do enough testing. My test data didn't have any contiguous empty/null fields.

After further testing:

SELECT replace(replace(

isnull(replace([c1],' ','#'),'#')

+','+isnull(replace([c2],' ','#'),'#')

+','+isnull(replace([c3],' ','#'),'#')

+','+isnull(replace([c4],' ','#'),'#')

+','+isnull(replace([c5],' ','#'),'#'),'#,',''),',#','')

FROM [recs]

The '#' is just a temporary filler - it could be any character that is unlikely to be used. The idea is to put a filler character where there are nulls and then replace where there is a null-filler and a comma that are together.

I couldn't get this to work with empty strings in the fields, but it will work if they are single spaces or nulls.

Bill

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 7

Not doin so good hahahah. now I get this:

################Butalbital#######################Methamphetamine###################

here is the query now..

SELECT records.result, company.company,

records.firstn + ' ' + records.lastn AS DONOR, records.donorid,

records.collecteddate, records.reason, records.lab,

records.speciminid,

REPLACE(REPLACE(ISNULL(REPLACE(records.amphetamine,

' ', '#'), '#') + ',' + ISNULL(REPLACE(records.butalbital, ' ', '#'),

'#') + ',' + ISNULL(REPLACE(records.cocaine, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.codeine, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.heroin, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.marijuana, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.methadone, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.methamphetamine, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.morphine, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.phencyclidine, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(records.propoxyphene, ' ', '#'), '#')

+ ',' + ISNULL(REPLACE(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,

records.verifysig

FROM records INNER JOIN

company ON records.mrocomid = company.mrocomid

WHERE (records.recordnum = ?)

what am I doing wrong? I have tried this a million ways, can't be that difficult

I'm hopeless

Kristin

Bill Nye Hall of Fame Points: 3248 More actions · Answer 8

Yes, this is trickier than I first thought it would be.

It looks like those empty fields are actually filled with spaces. I forgot to suggest including a trim function even though you had one earlier because all my data is perfect so it doesn't have a bunch of unnecessary spaces.

So, try adding back:

... RTRIM(records.drugname) ...

Bill