• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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')

Open in new window


I have to generate the recordset as

MedRecNo<Tab>Dispensedt<Tab>BB<Tab>H0004<Tab>QTY<Tab>UnitPrice  
 but the data should be in ASCII format.
0
Star79
Asked:
Star79
  • 5
  • 5
  • 3
  • +2
2 Solutions
 
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_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')

Open in new window

0
 
Star79Author Commented:
how to convert the result set to ascii
Thanks
0
 
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
What Kind of Coding Program is Right for You?

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.

 
Star79Author Commented:
hello jim, I want the columns to be returned as ASCII character
0
 
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
0
 
COBOLdinosaurCommented:
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.
0
 
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?
0
 
mlmccCommented:
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
0
 
Star79Author Commented:
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.
0
 
mlmccCommented:
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

65666709797172097374750976777809

mlmcc
0
 
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
0
 
mlmccCommented:
--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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Of course there are other tools than UltraEdit for this specific purpose, which are free, like HxD (and many other Hex Viewers).
0
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now