Solved

Retrieving full character value of field

Posted on 2006-10-23
7
134 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi: how to send PJL commands to printer 3 88
Show Listview image from database (String field) 5 112
oracle global variables 4 63
Delphi inherited method 6 59
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…
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 explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now