In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Solved
SQL RECORDSET IN ASCII FORMAT WITH SINGLE TAB DELIMITOR
Posted on 2013-10-24
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
3- +2
16 Comments
Active today
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_name
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')
Active today
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.
Active today
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?
Active today
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.
Active today
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
Active today
>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
Active today
--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
Active today
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.
Featured Post
Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month11 days, 5 hours left to enroll
718 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.