We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
10 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 5 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.