Question

I need to show this using SQL: Credit card numbers should include asterisks in place of all digits preceding the last four digits, which will be left visible, regardless of credit card length

Answer 1

As you have not shared your tabel schema here is my attempt to create one and take some action.

BEGIN TRANSACTION;

drop table card;
create table card(number varcar, name varchar);
insert into card (number, name) values ('12345678901234', 'abc') ;
insert into card (number, name) values ('12346678908907', 'pqr');
insert into card (number, name) values ('11111111111934', 'xyz');
COMMIT;

CREATE FUNCTION [dbo].[hideCardNumber](@InputString varchar(card))
RETURNS VARCHAR(card)
AS
BEGIN
declare @len int
,@Counter int
,@OneChar char(1)
set @Counter = 1
set @len = LEN(@card);
while (1 = 1)
begin
set @OneChar = SUBSTRING(@InputString, @Counter, 1)
set @InputString = REPLACE(@InputString, @OneChar, '*')
set @Counter = @Counter + 1

if (
@Counter > @len - 4
)
break;
end

return @InputString
END

select * from card;

select dbo.hideCardNumber(number) as num from card;