How do I append underscores based on the Level

• sqldbame

  SSC Rookie

  Points: 28

  More actions

  October 8, 2014 at 12:22 pm

  #294100

  I have a table that looks like below.

  DECLARE @Tree TABLE

  (

  NAME VARCHAR(100),

  [LEVEL] INT

  )

  INSERT INTO @Tree

  SELECT 'ABCD', 1

  UNION

  SELECT 'ABBBCDD', 2

  UNION

  SELECT 'AABBCCDD', 2

  UNION

  SELECT 'AAAABBBBCCCCDDDD', 3

  My question is based on the Level, I want to append that many _(underscores) before the name.

  Eg : for level 3 I want the name as ___AAAABBBBCCCCDDDD.

  I was thinking to insert this into a temp table and call a while statement based on the number. I wasn't sure if that was the optimal way. Please provide any inputs you have.

  Thanks in Advance.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 8, 2014 at 12:31 pm

  #1751078

  No need for a while loop, you just need to use REPLICATE() function.

  SELECT REPLICATE('_', [LEVEL]) + NAME

  FROM @Tree

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sqldbame

  SSC Rookie

  Points: 28

  More actions

  October 8, 2014 at 12:38 pm

  #1751082

  Luis Cazares (10/8/2014)

  ---

  No need for a while loop, you just need to use REPLICATE() function.

  SELECT REPLICATE('_', [LEVEL]) + NAME

  FROM @Tree

  That's awesome. Saved me a lot of work...

  Thank you so much.