SQLTeam.com | Weblogs | Forums

Sql query for masking 1st five charc in each cloumn

Hi Team,

need your help to create a query for I have a column called 'A' with 10 digits in length
I also have a column called 'B'

  A                                          B

1234567890 SAM
2345678912 HELLO
7364557382 MORNING

I want to mask column A and replace the first 5 digits with column B.

Post masking :
SAM 67890 (with 2 spaces)
HELLO78912
MORNI557382

this might work but looks awfully oogly

declare @boomba table(numeros numeric, names nvarchar(50) )

insert into @boomba
select 1234567890, 'SAMM' union
select 2345678912, 'HELLO' union
select 7364557382, 'MORNING' 

declare @coocoo char(3) = 'SAM'

/*
	SAM 67890 (with 2 spaces)
	HELLO78912
	MORNI557382
	SAM  67890
	
	
*/

select case  
		   when len(names) < 5 then names + stuff(' ',1,5-(len(names)+(5-len(names))), ' ') + cast(right(numeros,5) as varchar(20))
           else CONCAT(left(names,5), cast(right(numeros,5) as varchar(20)))
		end
   from @boomba

hi

hope this helps .. for any reason Great

declare @sample_data table ( A bigint , B varchar(100))

insert into @sample_data select 1234567890 ,'SAM'
insert into @sample_data select 2345678912 ,'HELLO'
insert into @sample_data select 7364557382 ,'MORNING'

select * from @sample_data


-- SQL 
    select 
            B 
    	  + '  '
    	  + right(cast( A as varchar),6)
    from 
         @sample_data

image

IF OBJECT_ID('tempdb..#table1') IS NOT NULL
    DROP TABLE #table1;
CREATE TABLE #table1 (
    A varchar(10) NOT NULL,
    B varchar(30) NULL
    );
INSERT INTO #table1 VALUES
    ('1234567890', 'SAM'),
    ('2345678912', 'HELLO'),
    ('7364557382', 'MORNING')

SELECT
    A,
    STUFF(A, 1, 5, LEFT(B + SPACE(5), 5)) AS New_A
FROM #table1
1 Like

this should do it

select left(names,5) + stuff(' ',1, 5,space(5 - len(names))) + cast(right(numeros,5) as varchar(20))
 from @boomba
1 Like