h1

SQL SERVER: REPLICATE function

02/26/2009

Today i read SQLAuthority.com, and I found that one developer
has this issue. Lets see that problem as well as the solution
for the same.

There is one numeric column. User needs to make
sure that all data should be of same size.

We need to display as:

17.00 TO 17.00
2.00 TO 02.00
8.17 TO 08.17

So I found REPLICATE function of SQL SERVER, to fix this.

How to use REPLICATE function?

REPLICATE (“string that you want TO append” ,”INTEGER
VALUE” )

FIRST parameter, i need TO
SET the CHARACTER, which
will append it BEFORE the NUMBER.

SECOND parameter, how many
times this CHARACTER should be ADD TO the NUMBER.

Example:
DECLARE @t AS NUMERIC(8,2)
SELECT @t = 08.2
SELECT Cast(Replicate(0,6-Len(@t)) AS VARCHAR(5)) + Cast(@t AS VARCHAR(5))

Here I specify that there should be 5 length. In this case 8.23 has four digit, so this will add one “0” to this number.

We can append any character by just changing the First Parameter, String value.

Let me know your suggestions

Advertisements

8 comments

  1. i want a proper example


  2. Hi Anil,

    Let say, you need to display Month. Month should always be in two digit.

    Like: Current Month is
    January, “01”,
    February “02”
    ..
    ..
    ..
    November “11”
    December “12” like that.

    In this Case, REPLICATE will be useful as:

    SELECT REPLICATE(‘0’,2 – LEN(MONTH(getdate()))) + CAST(MONTH(getdate()) AS VARCHAR)

    Here I used, “2 – LEN(MONTH(getdate()))”, as I only need to append 0, if I get month as one digit only.

    So, If Month has one digit, this function will append one zero, if month has two digit, then it will not.

    Let me know if you need more explanation.

    Thanks,

    Tejas


  3. Hi Tejas,
    The following select statement will also solve the same problem without using REPLICATE

    SELECT RIGHT(‘0’ + CAST(DATEPART(MI,GETDATE()) AS Varchar(2)), 2)

    You can think that as an alternative solution.


  4. We have a requirement of appending around three varchar columns of table with comma operator. Also we need to ignore the NULL or Empty Values during append. Suppose the table structure is as below

    declare @t table (col1 varchar(10),col2 varchar(10),col3 varchar(10))
    insert into @t
    select ‘test1′,’test2′,’test3’ union all
    select ‘test4′,null,’test5′ union all
    select null,null,’test6′ union all
    select ”,’test7’,”

    Expected result must be as below

    Result
    ——————————–
    test1,test2,test3,test4,test5,test6,test7


  5. HI Ganesh,
    Quick solution is:

    declare @t table (col1 varchar(10),col2 varchar(10),col3 varchar(10))
    insert into @t
    select ‘test1′,’test2′,’test3’ union all
    select ‘test4′,null,’test5′ union all
    select null,null,’test6′ union all
    select ”,’test7’,”

    SELECT ( ‘,’ + col1)
    FROM (
    select col1 from @t
    UNION
    select col2 from @t
    UNION
    select col3 from @t
    ) t2
    WHERE NULLIF(t2.Col1,”) IS NOT NULL
    ORDER BY t2.Col1
    FOR XML PATH(”)

    What I did is:
    1. I made one column for all values
    2. Use the same query

    Thanks,
    Tejas
    SQLYoga.com


  6. Nice answer. But is it slow when there are more rows to do.


  7. That is true, that’s why I said quick solution, not quick update 🙂

    Tejas
    SQLYoga.com


  8. I’ve learn some excellent stuff here. Definitely worth bookmarking for revisiting. I surprise how much attempt you set to create the sort of excellent informative website.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: