SELECT * FROM EMP_MTN WHERE PATINDEX('%[]N',EmpID) = 0 AND PATINDEX('%[]N',[EmpName]) = 0
Except
select * from EMP_MTN
where EmpID like '51%' OR EmpID like '85%' OR EmpID like 'TR%' OR EmpID like 'MY%' OR EmpID like 'CR%' or EmpID like 'JP%' or EmpID like '80%'
You don't give enough information. Update what column into what value?
Good day sir,
Sorry for the lack of information. Actually the below is my situation that I need help with.
- Convert all old data (without _N) to _OLD. Example, 000015 to 000015_OLD, same goes for the EmpName table, FURUSAKI to FURUSAKI_OLD
while excluding / ignoring certain character such as '85XXXX', '80XXXX', 'TRXXXX', 'CRXXXX', 'MY', etc
Basically, I wan amend the data with '00XXX' in front only.
- Convert all new data (with _N) to without '_N'. Example, 000001_N to 000001
if object_id ('tempdb..#EMP_MTN') is not null drop table #EMP_MTN
create table #EMP_MTN (EmpID varchar(255), [EmpName] varchar(255))
insert into #EMP_MTN values
('801124','Zukri bin Sulaiman_N'),
('JP0015','Masaru furusaki')
select * from #EMP_MTN
Update #EMP_MTN
set
EmpID = EmpID+'_OLD'
,[EmpName] = [EmpName]+'_OLD'
where right([EmpName],2) <> '_N'
select * from #EMP_MTN
Update #EMP_MTN
set
[EmpName] = substring([EmpName],0,charindex('_N',[EmpName]))
where right([EmpName],2) = '_N'
select * from #EMP_MTN