I had written article SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display few days ago. Previous article was answer to the email which I had received:
Dear Pinal,
I have situation where I need to display my numbers in fixed format. Like
1 as 0000001
109 as 0000109
0987 as 0000987
Is there any string function in SQL Server which can do this?
I had asked my blog readers to participate at the end of the article and I have received three very good suggestions. I am going to list all the three suggestions here. For me all the three suggestions are equally good. I suggest my readers to use any suggestion which they find easy to understand. I suggest to read my previous article before continuing this article further.
Solution 1 by Imran Mohammed
CREATE FUNCTION dbo.FnExample
(
@Size INT,
@Column_Value VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT RIGHT(REPLICATE(0, @Size)
+ CONVERT (VARCHAR(MAX), @Column_Value), @Size) AS ID
)
GO
-- Run following script to see output
SELECT * FROM dbo.FnExample (7, 123)
GO
Solution 2 by Fenil Desai
CREATE FUNCTION [dbo].[f_pad_before]
( @string VARCHAR(255),
@desired_length INTEGER,
@pad_character CHAR(1))
RETURNS VARCHAR(255) AS
BEGIN
/* Prefix the required number of spaces to bulk up the string and
then replace the spaces with the desired character */
RETURN LTRIM(RTRIM(
CASE
WHEN LEN(@string) < @desired_length
THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
ELSE @string
END
))
END
GO
-- Run following script to see output
SELECT dbo.f_pad_before('123',7,'0')
GO
Solution 3 by CodeMonkey
CREATE FUNCTION dbo.PadLeft
(@Value INT, @PadWidth INT, @PadChar CHAR(1))
RETURNS VARCHAR(255)
AS
BEGIN
RETURN ISNULL(REPLICATE(@PadChar, @PadWidth - LEN(@Value)), '')
+ CAST(@Value AS VARCHAR)
END
GO
-- Run following script to see output
SELECT dbo.PadLeft(123,7,0)
GO
Answer of all of the above script will be ’0000123′.
Again, I want to congratulate all three of my reader to participate in this interesting puzzle and would like to encourage other readers to participate in similar puzzles like this.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
When calling the function with less than the length: (123,7,0)
Solution 1 truncates the value.
Solution 2 and 3 return the value itself.
I am surprised noone used STUFF():
CREATE FUNCTION dbo.a
(
@Length TINYINT,
@Pad VARCHAR(1),
@Column_Value INT
)
RETURNS VARCHAR(255)
AS
BEGIN
RETURN STUFF
(
REPLICATE(@Pad, @Length),
@Length – LEN(@Column_Value),
LEN(@Column_Value),
@Column_Value
);
END
GO
select dbo.a(7, 0, 123);
drop function dbo.a
Very interesting article, i have bookmarked your blog for future referrence. Best regards
Another method
declare @col varchar(100), @size int
set @col=363453344234
set @size=18
select replace(str(@col,@size),’ ‘,’0′)
[...] SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display [...]
Hi,
This is my query for the same.
use AdventureWorks
go
alter procedure MYProc @value1 bigint
as
begin
declare @String1 varchar(10)
Declare @length bigint
Declare @TotalLength bigint
set @TotalLength = 10
set @length = @TotalLength – LEN(@value1)
set @String1 = REPLICATE (0,@length) + CONVERT (nvarchar(10),@value1)
print @String1
end
go
Exec Myproc 555
my requirement is
I am trying to figure out how to do looping in SQL.
I have table like below:
ID System Material
1 Floor Wood
1 Floor Carpet
1 Wall Dry wall
1 Wall Paper
2 Floor Wood
2 Floor Carpet
I ‘d like to end up with results like:
ID System Material
1 Floor Wood, Carpet
1 Wall Dry Wall, Paper
2 Floor Wood, Carpet
For this i need user defined function
Please give query
@chrsitopher
This should be simple. First learn to create comma-separated values. Pinal has an article on that here: http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/
create function dbo.HirsPadding
(
@size int,
@input varchar(max)
)
returns varchar(max)
as
begin
IF @size < LEN(@input)
RETURN RIGHT(@input,@size)
return REPLICATE(0,@size-LEN(@input))+@input
end
select dbo.HirsPadding(10,'12345')
[...] SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display [...]
[...] SQL SERVER – UDF – Pad Ride Side of Number with 0 – Fixed Width Number Display [...]