Left Padding(LPAD) using REPLICATE

Saturday, 19 September 2009

Here's a SQL UDF to do the left padding using REPLICATE.

create function [dbo].[leftpad]

(

@csrting nvarchar(4000)

, @nlen smallint

, @cpadcharacter nvarchar(4000) = ' '

)

returns nvarchar(4000)

as

begin

declare @nleftlen smallint, @nrightlen smallint

declare @length smallint, @lenpadcharacter smallint

select @length = datalength(@csrting)/(case sql_variant_property(@csrting,'basetype') when 'nvarchar' then 2 else 1 end)

select @lenpadcharacter = datalength(@cpadcharacter)/(case sql_variant_property(@cpadcharacter,'basetype') when 'nvarchar' then 2 else 1 end) -- for unicode

if @length >= @nlen

set @csrting = left(@csrting, @nlen)

else

begin

set @nleftlen = @nlen - @length

set @csrting = left(replicate(@cpadcharacter, ceiling(@nleftlen/@lenpadcharacter) + 2), @nleftlen)+ @csrting

end

return (@csrting)

end

Text Concatenation in SQL Server

Sunday, 17 May 2009

To concate the Text for a combination of keys there might be many ways.

Initially I was using either function or cursor for concatenation. But finally I found the below query most useful for the same.

First Create a table called Product with the following data.

Don't forget to create index if your table has huge data.

Then run the below Query:

SELECT
DISTINCT T.ID
,(SELECT ' ' + [DESC] FROM PRODUCT WHERE ID = T.ID FOR XML PATH('')) AS [DESC] FROM PRODUCT T

You will get the output as:



I hope this will help you guys. Share if you have any other good alternative.