SQL Server Performance

attach characters to string in begining

Discussion in 'General Developer Questions' started by d17may, Dec 20, 2006.

  1. d17may New Member

    I have field(nvarchar) in table which has maximum length of 8 characters .My need is that
    suppose if i have string 45 then sql server itself attach 6 zeroes and write 000000045 in the table.I can do it in procedures but there is any automatic mechanism or anyother better method .
    Thanks
    d17may, Dec 20, 2006
    #2

  2. Adriaan New Member

    You could use a trigger ...<br /><br />CREATE TRIGGER MyTrig ON dbo.MyTable<br />FOR INSERT, UPDATE<br />AS<br /><br />IF EXISTS (SELECT * FROM inserted WHERE LEN(MyField) &lt; <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />BEGIN<br />UPDATE T SET MyField = RIGHT(('0000000' + MyField), <img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />FROM dbo.MyTable T INNER JOIN inserted I<br />ON T.&lt;key&gt; = I.&lt;key&gt; WHERE LEN(I.MyField) &lt; 8<br />END
    Adriaan, Dec 20, 2006
    #2

  3. ranjitjain New Member

    I think just for this functionality, you should not go for trigger.<br />It is not going to be one time task right? so you better handle this in stored procedure itself.<br /><br />declare @val1 varchar(10)<br />set @val1='45'<br />select right('00000000'+@val1,<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />
    ranjitjain, Dec 20, 2006
    #2

  4. Adriaan New Member

    Fair enough. If the insert/update is happening through a sproc, handle it there.
    Adriaan, Dec 20, 2006
    #2

  5. FrankKalis Moderator

    For the records, you can also do something like <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT REPLACE(STR(@val1, <img src='/community/emoticons/emotion-11.gif' alt='8)' />, ' ', '0')<br /></font id="code"></pre id="code"><br />But the more interesting question is, why you would want to store such data as you can easily derive that at run-time?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Dec 20, 2006
    #2

  6. Adriaan New Member

    Probably to fake numeric ordering on an alphanumeric column.
    Adriaan, Dec 20, 2006
    #2

  7. FrankKalis Moderator

    This, or this is for some exchange format. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Dec 20, 2006
    #2

  8. Madhivanan Moderator


    1 You dont need to use nvarchar datatype if you store only numbers
    2 Store numbers as such and it is your front end where you should format the data
    3 You dont need to automate the process if you consider above methods

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Dec 20, 2006
    #2

  9. FrankKalis Moderator

    Madhi, do you have an answer-bot for these kind of answers? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Dec 20, 2006
    #2

  10. d17may New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />For the records, you can also do something like <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT REPLACE(STR(@val1, <img src='/community/emoticons/emotion-11.gif' alt='8)' />, ' ', '0')<br /></font id="code"></pre id="code"><br />But the more interesting question is, why you would want to store such data as you can easily derive that at run-time?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />This question is asked by my friend .I also don't know the reason behind attaching <br />zeroes .Since i don't know how to do it i posted here .Thanks all of you for your kind <br />help.<br /><br />Thanks<br />d17may
    d17may, Dec 20, 2006
    #2

  11. 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 FrankKalis</i><br /><br />Madhi, do you have an answer-bot for these kind of answers? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes. Now-adays, these type of questions are asked frequently<br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
    Madhivanan, Dec 21, 2006
    #2

  12. 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 FrankKalis</i><br /><br />For the records, you can also do something like <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT REPLACE(STR(@val1, <img src='/community/emoticons/emotion-11.gif' alt='8)' />, ' ', '0')<br /></font id="code"></pre id="code"><br />But the more interesting question is, why you would want to store such data as you can easily derive that at run-time?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Did you know why this gives strange result without any truncation or Error message?<br />Select Replace(str(33247448,7),' ',0)<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
    Madhivanan, Dec 21, 2006
    #2

  13. 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 FrankKalis</i><br /><br />For the records, you can also do something like <br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT REPLACE(STR(@val1, <img src='/community/emoticons/emotion-11.gif' alt='8)' />, ' ', '0')<br /></font id="code"></pre id="code"><br />But the more interesting question is, why you would want to store such data as you can easily derive that at run-time?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Did you know why this gives strange result without any truncation or Error message?<br />Select Replace(str(33247448,7),' ',0)<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
    Madhivanan, Dec 21, 2006
    #2

  14. FrankKalis Moderator

    You're trying to push an 8 bytes input expression (that evaluates to an INT value) into a 7 seven bytes character result expression. AS you can see, obviously this doesn't work. By default doesn't SQL Server throw an error message when to try to convert an INT expression into a VARCHAR expression, but rather displays the '*'.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Dec 21, 2006
    #2

  15. Madhivanan Moderator

    Thanks. I put some more examples in this regard

    Declare @s varchar(7)
    set @s=33247448
    select @s

    set @s='33247448'
    select @s

    But I always expect "Error Messages"

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Dec 21, 2006
    #2

  16. FrankKalis Moderator

    Quote from CAST and CONVERT in BOL. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.<br /><br />From data type To data type Result <br />int, smallint, or tinyint char * <br /> varchar * <br /> nchar E <br /> nvarchar E <br />money, smallmoney, numeric, decimal, float, or real char E <br /> varchar E <br /> nchar E <br /> nvarchar E <br /><br /><br />* Result length too short to display.<br />E Error returned because result length is too short to display.<br /><br />Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release. This example shows such a roundtrip conversion:<br /><br />DECLARE @myval decimal (5, 2)<br />SET @myval = 193.57<br />SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))<br />-- Or, using CONVERT<br />SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Dec 21, 2006
    #2

  17. FrankKalis Moderator

    Sorry, the tabular format got lost. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />Read about in in BOL. <br />...and don't try too hard to figure out, why things happen the way they do. It's "by design". So, if you can't fix it, feature it. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Dec 21, 2006
    #2

Share This Page