SQL RECORDSET IN ASCII FORMAT WITH SINGLE TAB DELIMITOR
Hello I have the below sql query:
select P.MedRecNo ,CONVERT(char(10),DispenseDt,101) as Dispensedt,'BB','H0004',(CAST(Qty AS INT)) as QTY,Qty/TtlPrice as UnitPrice from Billing B left join Patients p on B.PatID = p.PatID and B.FacID = p.FacID where B.facid ='1' and B.MOP='FACI' and Billed between '2013-9-1 00:00:00' and '2013-9-30 00:00:00' and B.InvoiceGrp in ('MEDA','MGCARE')
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The low-tech solution would be to just throw a tab character between each column, and return it as a single column
Declare @tab char(1) = char(9) select P.MedRecNo + @tab + CONVERT(char(10),DispenseDt,101) + @tab + 'BB'+ @tab +'H0004'+ @tab +(CAST(Qty AS INT)) + @tab + CAST(Qty/TtlPrice as varchar(max)) as column_namefrom Billing B left join Patients p on B.PatID = p.PatID and B.FacID = p.FacIDwhere B.facid ='1' and B.MOP='FACI' and Billed between '2013-9-1 00:00:00' and '2013-9-30 00:00:00' and B.InvoiceGrp in ('MEDA','MGCARE')
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Explain what you mean by 'result set to ascii'.
If you want the string '<Tab>' instead of the tab character, then just replace the above with..
Declare @tab char(5) = '<tab>'
0
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us an example of the output you wish to see.
0
Star79Author Commented:
sorry i dont have an example..just that I have the requirement whihc is the same query as you made with the tab but the columns need to be convered to ASCII
Is this some kind of a homework assignment? The Experts are limited in what they can do for homework questions.
The expert has ask you to post an example and if you can provide that, how do you know when a solution is working? What are you going to compare it to?
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Are you trying to say you need the result exported into a file, coded in your current code page (because ASCII is a 7bit subset of the 8bit code page representation in non-Unicode file formats)? If so, which tool are you using? Is commandline ok?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>yes I want something like ... ABC<tab>DEF
And you were given it in the very first comment.
>sorry i dont have an example..just that I have the requirement whihc
Demonstrate for us that you know what you are asking for, because right now we're not convinced, and an example output would be an excellent place to start.
'I have a requirement' without giving us an example smells like homework, and my mind-reading skills have some limitations.
0
Star79Author Commented:
thanks mlmcc..
infact Iam confused too
Text files stored in ASCII format are called ASCII files.
So once I run the first comment query and copy the results to a notepad and save as test.txt
Is test.txt in ASCII format
Remember ASCII refers to the representation of the character set in the computer (binary) and not what you and I see in Notepad.
To see it in true ASCII representation you need to look at it in a HEX editor which shows the actually encoding.
I could store files in EBCIDIC format or encoding, IBM mainframes of old did that. It is just a different encoding scheme and the ordering of the character set is different. You can see the difference in the encoding at this link
Open in new window