SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Lpad in views

  1. #1
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lpad in views

    Hi, I'm having a little silly difficulty here.


    Code SQL:
    SELECT
    	  LPAD(`Tickets`.`Row`,2,'00') AS `Row`
      FROM (`Tickets`)

    If I run this as a query I get the expected results (01,02,03,04,05,06....)

    However the same thing as a view:
    Code SQL:
    CREATE VIEW `blarg` AS
    SELECT
    	  LPAD(`Tickets`.`Row`,2,'00') AS `Row`
      FROM (`Tickets`)

    I get this result
    0x3238 all the way down my table.

    I'm assuming I need to convert this number in order for it to work, any ideas?
    Please...Never describe anything to me using foo and bar.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why not just create your data type as ZEROFILL?

  3. #3
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    why not just create your data type as ZEROFILL?
    how would I get it to display the zeros?

    I need them because I am using concat to put two values together but I want to make sure that they keep their number places
    Please...Never describe anything to me using foo and bar.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look up ZEROFILL and find out.

  5. #5
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    Look up ZEROFILL and find out.
    I've tried to look up how to display the zerofill literally 'display zerofill mysql' in google and I didn't find anything that was a clear cut answer, I just find a description of the zerofill but not how to handle it. Any suggestions?
    Please...Never describe anything to me using foo and bar.

  6. #6
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok finally found it.
    It is suppose to happen automatically, and the sql program I am using isn't creating the tables like it says it is.
    What are your thoughts on this quote I found:
    ZEROFILL is a pretty esoteric feature, supported by no other database server besides
    MySQL. My recommendation is avoid using it.

    Instead pass the input data through LPAD(), where you can pad with zeroes (or
    anything else), which will also change the numerical data into a string. This
    preserves the zeros when data hits the database layer (Zeos) in HeidiSQL.
    Please...Never describe anything to me using foo and bar.

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you bother to consult the manual?

  8. #8
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I consult the manual and google. I was able to do it with the zerofill suggestion. Only that concating a zerofill does the same thing as lpad. 0x00

    So I'm back to square one
    Please...Never describe anything to me using foo and bar.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by prince_mallow View Post
    ...because I am using concat to put two values together but I want to make sure that they keep their number places
    can you show the code where this is happening, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •