format results of query
-
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
-
you could try
isnull(rtrim(records.amphetamine), ' ')
I think this should work.
regards
Shaji
-
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
-
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
-
tells me isnull requires two arguments????
what does that mean?
Kristin
-
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
-
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
-
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
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply