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.
SQL RECORDSET IN ASCII FORMAT WITH SINGLE TAB DELIMITOR
Hello I have the below sql query:
I have to generate the recordset as
MedRecNo<Tab>Dispensedt<Ta
but the data should be in ASCII format.
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')
I have to generate the recordset as
MedRecNo<Tab>Dispensedt<Ta
but the data should be in ASCII format.
Asked:
5-
5
3- +2
2 Solutions
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>'
If you want the string '<Tab>' instead of the tab character, then just replace the above with..
Declare @tab char(5) = '<tab>'
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?
COBOLdinosaur, Topic Advisor.
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?
COBOLdinosaur, Topic Advisor.
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?
Do you want the output to be like
ABC<tab>DEF
065 066 067 009 068 069 070
If so what tools do you want to do this in? Do you want it to be just a SQL command or can a .Net application be written to do the output?
mlmcc
ABC<tab>DEF
065 066 067 009 068 069 070
If so what tools do you want to do this in? Do you want it to be just a SQL command or can a .Net application be written to do the output?
mlmcc
hello mlmcc,
yes I want something like
ABC<tab>DEF
yes the abc and def should be ASCII and I will have to use the sql command only as we dont use .net here
The client wants a data extract in ascii format delimited by tab.
Thanks.
yes I want something like
ABC<tab>DEF
yes the abc and def should be ASCII and I will have to use the sql command only as we dont use .net here
The client wants a data extract in ascii format delimited by tab.
Thanks.
I think your use of the term ASCII is confusing. ASCII is a character encoding used by computers to internally represent alphanumeric data.
If I create a text file and look a it in Notepad what would it show?
ABC DEF GHI JKL
The spaces between are tabs
I believe the first comment should provide that output
or do you want to see the ASCII values of the characters as in
65666709797172097374750976
mlmcc
If I create a text file and look a it in Notepad what would it show?
ABC DEF GHI JKL
The spaces between are tabs
I believe the first comment should provide that output
or do you want to see the ASCII values of the characters as in
65666709797172097374750976
mlmcc
>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.
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.
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
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
--Is test.txt in ASCII format?
Yes
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
http://publib.boulder.ibm.com/infocenter/lnxpcomp/v8v101/index.jsp?topic=%2Fcom.ibm.xlf101l.doc%2Fxlflr%2Fasciit.htm
mlmcc
Yes
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
http://publib.boulder.ibm.com/infocenter/lnxpcomp/v8v101/index.jsp?topic=%2Fcom.ibm.xlf101l.doc%2Fxlflr%2Fasciit.htm
mlmcc
UltraEdit is a super text editor app, which offers a 30-day free trial.
Download the app, then open it, then open any text file, then hit {ctrl}-H to view all characters in the file in Hex.
I've used this app multiple times when dealing with files created by various mainframes that insert characters that SSIS could not handle.
Download the app, then open it, then open any text file, then hit {ctrl}-H to view all characters in the file in Hex.
I've used this app multiple times when dealing with files created by various mainframes that insert characters that SSIS could not handle.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Open in new window