Retrieving full character value of field

Posted on 2006-10-23
Last Modified: 2010-04-05
How does one retrieve a char field from a mysql table with the data padded to the full length?  I know this is probably simple, but I can't seem to figure it out.

Question by:ajtsoukalas
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
  • 3
  • 2
  • 2

Author Comment

ID: 17788981
I should mention from Delphi 7.
LVL 14

Expert Comment

ID: 17789286
SELECT RPAD(Yourcolumn,100,' ') from yourtable;

LVL 32

Expert Comment

ID: 17790365
racek's proposal works fine if you have character datatype and want the retrieved values to be left-justified and blank-filled. Sometimes character fields may represent numeric values that you want to precede with leading zeroes. In this case, you would use -
select lpad(yourcolumn,N,'0') where N represents an integer equal to the total number of characters you want to display.
Industry Leaders: We Want Your Opinion!

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!


Author Comment

ID: 17792739
I guess I am confused

I use SQLyog to create the database and tables.  it's a nice little tool.  However when I execute the following SQL statement,

SELECT RPAD(facility, 100, '1')  from mkappointments

and export the result to a csv file which I read with excel, the column is 100 characters long, not 30 (which is the size of the char field)

Is this SQLyog not working or am I missing something else.


LVL 14

Accepted Solution

racek earned 500 total points
ID: 17793602
SELECT RPAD(facility, 100, '1')  from mkappointments is producing a new column - 100 char long. You can set a name on this new column:
SELECT RPAD(facility, 100, '1') AS facility100  from mkappointments..

IF facility is 'bath', facility100 will be 'bath1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111'

LVL 32

Expert Comment

ID: 17796293
select rpad(facility,100,'1') says take whatever is in the field, facility, and make it 100 characters long filling whatever is necessary to do that with ones.
I think you want to simply say,
select rpad(facility, 30,'1')
,although I can't think of a reason why you would want to pad with ones.

Author Comment

ID: 17805154
I see

I thought you were specifying 100 as a catch all, and that the it would truncate after the field width.
I was hoping to not use constants so if I changed the fieldsize in the table, I would not have to change the program.

I solved the problem by reading the fieldsize and padding to it but it costs me an additional instruction for each field that needs to be padded.

It's possible that I could read in the fieldsizes for the fields in question before the select and then use those variables as the middle argument
but since I am processing each row in the select, I added a refinement to pad only to the longest one rather than the full fieldlength.  That seems to be more appealing visually.

I was just padding with 1's while testing because they are easier to visually count.

I guess I should have been more specific in the original question.

Since you did answer the question as posed, I am going to give you the points.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Firemonkey webbrowser scrollbars ? 1 67
CheckListBox usage 3 96
Firemonkey DbLookupComboBox equivalent ? 2 77
How to save the image in the .cds File ClientDataSet? 1 40
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question