How to padleft in T-SQL
-
Hi all,
This could be a very simple question to you.
How to pad a 0 to left of string?
Say, I have a column (Char(5)), contains a string like '9876','1647'.... I want to pad a zero to those string <'10000'
Thank you.
-
If your column is defined as char(5), then the data is actually going to be
'9876 ' or '1647 ' ** note trailing blanks
SQL automatically pads out CHAR fields with trailing spaces.
You could do something like this
CASE WHEN LEN(RTRIM([fieldname])) < 5 THEN RIGHT('00000' + RTRIM([fieldname]), 5) ELSE [fieldname] END--------------------
Colt 45 - the original point and click interface
-
Or just simply
RIGHT('00000' + RTRIM([fieldname]), 5)
As you don't really care if it is already 5 it will be five either way when done.
-
May I ask why you store numbers in a CHAR(5) column anyway?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank,
This situation comes up frequently in the U.S. where personal identification numbers assigned by the government (social security numbers) are always nine digits, and may begin with a zero. The numbers are an identifier, and are not used in computations. If the calling application happens to store the value in a numeric variable then the leading zero is often removed before storage in the DB.
Have a great day!
Wayne
-
Hm, yes, sure, there might be such valid reasons. However, I guess most of the time it is just laziness or something like that.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply