Tricky SQL Server Questions:

1) How to fin the sum of the Postive and Negetive numers from the columns?

create table #temp(num int);
insert into #temp values(-1);insert into #temp values(-2);
insert into #temp values(-3);insert into #temp values(-4);
insert into #temp values(1);insert into #temp values(2);
insert into #temp values(3);insert into #temp values(4)

select * from #temp

-1
-2
-3
-4
1
2
3
4

Select SUM(CASE WHEN num<0 0="" as="" else="" end="" negitive="" num="" then="" when="">0 then num ELSE 0 END) as POSITIVEfrom #temp ;
Output : NEGITIVE POSITIVE
-10 10

2. How does one remove special characters in SQL SERVER?

DECLARE @str VARCHAR(25)SET @str = '(Srik) anth-kota &l123'WHILE PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( '%[~,@,#,$,%,&,*,(,)]%', @str ), 1 ),''),'-',' ')
SELECT @str

Or

select 1 as id,'qw2!"$%^&**(' as F into #TESTTABLEinsert #TESTTABLE values (2, 'xxx'),(3, ''), (4,'$'),(5,'qq""ee$$');with cte(id, stripped) as (select id, cast(F as varchar(1024)) from #TESTTABLE
union all
select id, cast(stuff(stripped, patindex('%[^a-z]%', stripped), 1, '') as varchar(1024))from cte
where patindex('%[^a-z]%', stripped) > 0)select * from cte
where patindex('%[^a-z]%', stripped) = 0order by id

3) To remove quotes, use two quotes for every single quote as shown below:

CREATE
TABLE #temp(quote VARCHAR(5));
INSERT INTO #temp VALUES ('L''uck');
SELECT * FROM #temp;

SELECT REPLACE(quote,'''','') from #temp;