I need to convert int datafield to nvarchar with leading zeros
example:
1 convert to '001'
867 convert to '000867', etc.
thx.
This is my response 4 Hours later ...
I tested this T-SQL Script and work fine for me !
DECLARE @number1 INT, @number2 INT
SET @number1 = 1
SET @number2 = 867
SELECT RIGHT('000' + CAST(@number1 AS NCHAR(3)), 3 ) AS NUMBER_CONVERTED
SELECT RIGHT('000000' + CAST(@number2 AS NCHAR(6)), 6 ) AS NUMBER_CONVERTED
I created this user function
T-SQL Code :
CREATE FUNCTION CIntToChar(@intVal BIGINT, @intLen Int) RETURNS nvarchar(20)
AS
BEGIN
-- BIGINT = 2^63-1 (9,223,372,036,854,775,807) Max size number
-- @intlen contains the string size to return
IF @intlen > 20
SET @intlen = 20
RETURN REPLICATE('0',@intLe开发者_开发知识库n-LEN(RTRIM(CONVERT(nvarchar(20),@intVal))))
+ CONVERT(nvarchar(20),@intVal)
END
Example :
SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID
OUTPUT
000867
Try this: select right('00000' + cast(Your_Field as varchar(5)), 5)
It will get the result in 5 digits, ex: 00001,...., 01234
You can also use FORMAT() function introduced in SQL Server 2012. http://technet.microsoft.com/library/hh213505.aspx
DECLARE @number1 INT, @number2 INT
SET @number1 = 1
SET @number2 = 867
SELECT FORMAT(@number1, 'd10')
SELECT FORMAT(@number2, 'd10')
Use REPLICATE
so you don't have to hard code all the leading zeros:
DECLARE @InputStr int
,@Size int
SELECT @InputStr=123
,@Size=10
PRINT REPLICATE('0',@Size-LEN(RTRIM(CONVERT(varchar(8000),@InputStr)))) + CONVERT(varchar(8000),@InputStr)
OUTPUT:
0000000123
Not to High-Jack this question but the note needs to be made that you need to use (N)VARCHAR instead of (N)CHAR data-type.
RIGHT('000' + CAST(@number1 AS NCHAR(3)), 3 )
Above segment, will not produce the correct response from SQL 2005
RIGHT('000' + CAST(@number1 AS NVARCHAR(3)), 3 )
Above segment, will produce the desired response from SQL 2005
The the varchar will provide you with the desired prefix length on the fly. Where as the fixed length data type will require length designation and adjustments.
I like to use
DECLARE @Length int
DECLARE @Number int
SET @Length = 9
SET @Number = 4
select right( POWER(10, @Length) + @Number, @Length)
this gives me
000000004
Data type "int" cannot be more than 10 digits, additionally the "Len()" function works on ints, so there's no need to convert the int to a string before calling the len() function.
Lastly, you are not taking into account the case where the size of your int > the total number of digits you want it padded to (@intLen).
Therefore, a more concise / correct solution is:
CREATE FUNCTION rf_f_CIntToChar(@intVal Int, @intLen Int) RETURNS nvarchar(10)
AS
BEGIN
IF @intlen > 20 SET @intlen = 20
IF @intlen < LEN(@intVal) RETURN RIGHT(CONVERT(nvarchar(10), @intVal), @intlen)
RETURN REPLICATE('0', @intLen - LEN(@intVal)) + CONVERT(nvarchar(10), @intVal)
END
Not very elegant, but I add a set value with the same number of leading zeroes I desire to the numeric I want to convert, and use RIGHT function.
Example:
SELECT RIGHT(CONVERT(CHAR(7),1000000 + @number2),6)
Result: '000867'
One line solution (per se) for SQL Server 2008 or above:
DECLARE @DesiredLenght INT = 20;
SELECT
CONCAT(
REPLICATE(
'0',
(@DesiredLenght-LEN([Column])) * (1+SIGN(@DesiredLenght-LEN([Column])) / 2) ),
[Column])
FROM Table;
Multiplication by SIGN
expression is equivalent to MAX(0, @DesiredLenght-LEN([Column]))
. The problem is that MAX()
accepts only one argument...
Works in SQLServer
declare @myNumber int = 123
declare @leadingChar varchar(1) = '0'
declare @numberOfLeadingChars int = 5
select right(REPLICATE ( @leadingChar , @numberOfLeadingChars ) + cast(@myNumber as varchar(max)), @numberOfLeadingChars)
Enjoy
Had same issue, this is how I resolved... Simple and elegant. The "4" is how long the string should be, no matter what length of integer is passed it will pad with zero's up to "4".
STUFF(SomeVariableOrField,1,0,REPLICATE('0',4 - LEN(SomeVariableOrField)))
In my case I wanted my field to have leading 0's in 10 character field (NVARCHAR(10)). The source file does not have the leading 0's needed to then join to in another table. Did this simply due to being on SQL Server 2008R2:
Set Field = right(('0000000000' + [Field]),10) (Can't use Format() as this is pre SQL2012)
Performed this against the existing data. So this way 1 or 987654321 will still fill all 10 spaces with leading 0's.
As the new data is being imported & then dumped to the table through an Access database, I am able to use Format([Field],"0000000000") when appending from Access to the SQL server table for any new records.
select right('000' + convert(varchar(3),id),3) from table
example
declare @i int
select @i =1
select right('000' + convert(varchar(3),@i),3)
BTW if it is an int column then it will still not keep the zeros Just do it in the presentation layer or if you really need to in the SELECT
DECLARE @number1 INT, @number2 INT
SET @number1 = 1
SET @number2 = 867
-- Without the 'RTRIM', the value returned is 3__
!!!
SELECT RIGHT('000' + RTRIM(CAST(@number1 AS NCHAR(3)), 3 )) AS NUMBER_CONVERTED
-- Without the 'RTRIM', the value returned is 867___
!!!
SELECT RIGHT('000000' + RTRIM(CAST(@number2 AS NCHAR(6)), 6 )) AS NUMBER_CONVERTED
Using RIGHT
is a good option but you can also do the following:
SUBSTRING(CAST((POWER(10, N) + value) AS NVARCHAR(N + 1)), 2, N)
where N
is the total number of digits you want displayed. So for a 3-digit value with leading zeros (N = 3
):
SUBSTRING(CAST((POWER(10, 3) + value) AS NVARCHAR(4)), 2, 3)
or alternately
SUBSTRING(CAST(1000 + value) AS NVARCHAR(4)), 2, 3)
What this is doing is adding the desired value to a power of 10 large enough to generate enough leading zeros, then chopping off the leading 1.
The advantage of this technique is that the result will always be the same length, allowing the use of SUBSTRING
instead of RIGHT
, which is not available in some query languages (like HQL).
This work for me in MYSQL:
FUNCTION leadingZero(format VARCHAR(255), num VARCHAR(255))
RETURNS varchar(255) CHARSET utf8
BEGIN
return CONCAT(SUBSTRING(format,1,LENGTH(format)-LENGTH(num)),num);
END
For example:
leadingZero('000',999); returns '999'
leadingZero('0000',999); returns '0999'
leadingZero('xxxx',999); returns 'x999'
Hope this will help. Best regards
I was wondering, would this be on any use to you?
declare @val int,@len=800
select replace(str(@val,@len),' ','0')
精彩评论