[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

Retrieving full character value of field

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
ajtsoukalas
Asked:
ajtsoukalas
  • 3
  • 2
  • 2
1 Solution
 
ajtsoukalasAuthor Commented:
I should mention from Delphi 7.
0
 
racekCommented:
SELECT RPAD(Yourcolumn,100,' ') from yourtable;

0
 
awking00Commented:
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
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!

 
ajtsoukalasAuthor Commented:
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
 
racekCommented:
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
 
awking00Commented:
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
 
ajtsoukalasAuthor Commented:
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

Independent Software Vendors: 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!

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