?
Solved

Retrieving full character value of field

Posted on 2006-10-23
7
Medium Priority
?
149 Views
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.

AJ
0
Comment
Question by:ajtsoukalas
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

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

Expert Comment

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

0
 
LVL 32

Expert Comment

by:awking00
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.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

Author Comment

by:ajtsoukalas
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.

AJ

0
 
LVL 14

Accepted Solution

by:
racek earned 2000 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'

0
 
LVL 32

Expert Comment

by:awking00
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.
0
 

Author Comment

by:ajtsoukalas
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.

AJ
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Suggested Courses

601 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