SQL Server Performance

tsql-Query

Discussion in 'General Developer Questions' started by Reddy, May 10, 2006.

  1. Reddy New Member

    Hi
    I have a column where it can have 10 digits numerical value, when a user enter only 5 digits instead of 10 from his application, i want to display 0's for the remaining places where vlaue is not entered, How can I write a query for that?

    Thanks!
    "He laughs best who laughs last"

  2. FrankKalis Moderator

  3. Madhivanan Moderator

    If possible do this in front end application

    Madhivanan

    Failing to plan is Planning to fail
  4. Reddy New Member

    an update..how can i do this if i need 0's 1st and number later like 00001234

    Thanks!
    "He laughs best who laughs last"

  5. Adriaan New Member

    You are aware that if the column is of a numeric data type, and you want to store the leading zeroes, the leading zeroes will not "hold"?

    If your column is already of a character type, you can use either one of these:

    RIGHT('0000000000' + column, 10)

    REPLICATE('0', 10 - LEN(column)) + column



    If the column is numeric, then the first one is probably easier on the brain:

    RIGHT('0000000000' + CAST(column AS VARCHAR(10)), 10)
  6. Madhivanan Moderator

    DECLARE @MeineZahl INT<br />SET @MeineZahl = 12345<br />SELECT replace(str(@MeineZahl,10),' ','0')<br /><br />But this is front end issue [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  7. Adriaan New Member

    Madhivanan,

    I agree it might be a front-end issue, but it might also be a data conversion issue.
  8. cmdr_skywalker New Member

    to ensure domain integrity (i.e. incase you perform matching), enforce this rule either through your application or trigger. If you can't touch the application, use the trigger using the code suggested by Adriaan.<br /><br />FrankKalis, being picky on the 2+2+2+2+2, well, i think this one is leading zeroes not trailing zeroes <img src='/community/emoticons/emotion-5.gif' alt=';)' />.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  9. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Madhivanan,<br /><br />I agree it might be a front-end issue, but it might also be a data conversion issue.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. But when I ask "Where do you want to show the data?", mostly the reply is "Front end application or Reports" [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  10. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />FrankKalis, being picky on the 2+2+2+2+2, well, i think this one is leading zeroes not trailing zeroes <img src='/community/emoticons/emotion-5.gif' alt=';)' />.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />See, I have the advantage that I can almost always blame it on the language barrier. You know, me not talking English very good... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />I simply misunderstood the question. I interpreted "remaining places" = "trailing zeros". An issue that wasn't that clear at the time I first responded.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  11. Madhivanan Moderator

    &gt;&gt;FrankKalis, being picky on the 2+2+2+2+2, well, i think this one is leading zeroes not trailing zeroes <img src='/community/emoticons/emotion-5.gif' alt=';)' />.<br /><br /><br />What do you mean by 2+2+2+2+2?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  12. FrankKalis Moderator

  13. cmdr_skywalker New Member

    I am just teasing you back Frank <img src='/community/emoticons/emotion-1.gif' alt=':)' />. In reality, we survive in this field because we developed the ability to know the difference between UNION and UNION ALL and when to use them. But since we are communicating, its the idea that counts rather than words (except for syntax).<br /><br />By the way, its actually 2+2+2+2 <img src='/community/emoticons/emotion-1.gif' alt=':)' />.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com
  14. FrankKalis Moderator

    I know. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />...at least we've developed this ability after being burned once or more using the wrong UNION. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Share This Page