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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

726 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