Reputation: 1813
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
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