CODEWITHSUNDEEP
sql-server-2005t-sql
User13839404
User13839404

Reputation: 1813

How to add trailing space after some number of character using select statement?

In each column, after first 5 characters I need to add/replace next 5 characters with '*' then add 10 more white spaces.

SELECT nameFirst, nameLast, number, fax 
FROM table_01

Expected result:-

Revol*****          Jenni*****          598-1*****          12345*****      
Jenit*****          Austi*****          598-1*****          12345*****          
Revol*****          Jenni*****          598-1*****          12345*****

Thanks.

Upvotes: 3

Views: 11998

Answers (1)

JohnFx
JohnFx

Reputation: 34917

This should do it.

SELECT 
   LEFT(nameFirst,5) + '*****          ',
   LEFT(nameLast,5) + '*****          ',
   LEFT(number,5) + '*****          ',
   LEFT(fax ,5) + '*****          '
FROM table_01

If you think you might want to use a variable later for the number of * and space characters, this technique might be better (and more readable) but uses keywords that are specific to tSQL: 

SELECT 
   LEFT(nameFirst,5) + REPLICATE('*',5) + SPACE(10),
   LEFT(nameLast,5) + REPLICATE('*',5) + SPACE(10),
   LEFT(number,5) + REPLICATE('*',5) + SPACE(10),
   LEFT(fax ,5) + REPLICATE('*',5) + SPACE(10)
FROM table_01

Update per your comment
Assuming you want extra *'s for names with <5 characters. Here is an approach that should work. 

SELECT 
   LEFT(nameFirst + replicate('*',5),5) + replicate('*',5) + SPACE(10)
   ...
FROM table_01

It is a little sneaky, but it avoids a longer inline conditional.

Upvotes: 4

Related Questions