SQL Server Performance Forum – Threads Archive

tsql-Query

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"

Something like this?
DECLARE @MeineZahl INT
SET @MeineZahl = 12345
SELECT LEFT(CAST(@MeineZahl AS VARCHAR(10)) + REPLICATE(‘0’,10) ,10) ———-
1234500000 (1 row(s) affected) —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

If possible do this in front end application Madhivanan Failing to plan is Planning to fail

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"

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)

DECLARE @MeineZahl INT SET @MeineZahl = 12345 SELECT replace(str(@MeineZahl,10),’ ‘,’0′) But this is front end issue [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Madhivanan, I agree it might be a front-end issue, but it might also be a data conversion issue.

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. 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=’

‘ />. May the Almighty God bless us all! www.empoweredinformation.com

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Madhivanan, I agree it might be a front-end issue, but it might also be a data conversion issue. <hr height="1" noshade id="quote"></blockquote id="quote"> 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=’

‘ />] Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> 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=’

‘ />. <hr height="1" noshade id="quote"></blockquote id="quote"> 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=’

‘ />] I simply misunderstood the question. I interpreted "remaining places" = "trailing zeros". An issue that wasn’t that clear at the time I first responded. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

>>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=’

‘ />. What do you mean by 2+2+2+2+2? Madhivanan Failing to plan is Planning to fail

Take a look here:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14288&whichpage=2 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

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). By the way, its actually 2+2+2+2 <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. May the Almighty God bless us all! www.empoweredinformation.com

I know. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] …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=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>