Ravee123
asked on
T-SQL Query - Trim
Create TABLE #Ram (ID int, Stat Varchar(30), Lbl varchar(500))
INSERT INTO #Ram(ID,Stat, Lbl)
VALUES (1, 'null','Rahul,Southern'),
(2,'UNKNOWN','Ram,Rishi,Ta pas'),
(3, 'UNK', 'Dhoni'),
(4, ' ', 'Ilayaraja')
SELECT ID,
case when Stat = null then 'UNKNOWN'
WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
WHEN Stat = 'UNK' THEN 'UNKNOWN'
WHEN Stat = ' ' THEN 'UNKNOWN'
ELSE 'UNKNOWN'
END,
RTRIM(Lbl ',')
From #Ram
The Expected output
1 UNKNOWN Rahul
2 UNKNOWN Ram
3 UNKNOWN Dhoni
4 UNKNOWN Ilayaraja
Kindly help me to write a query.
INSERT INTO #Ram(ID,Stat, Lbl)
VALUES (1, 'null','Rahul,Southern'),
(2,'UNKNOWN','Ram,Rishi,Ta
(3, 'UNK', 'Dhoni'),
(4, ' ', 'Ilayaraja')
SELECT ID,
case when Stat = null then 'UNKNOWN'
WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
WHEN Stat = 'UNK' THEN 'UNKNOWN'
WHEN Stat = ' ' THEN 'UNKNOWN'
ELSE 'UNKNOWN'
END,
RTRIM(Lbl ',')
From #Ram
The Expected output
1 UNKNOWN Rahul
2 UNKNOWN Ram
3 UNKNOWN Dhoni
4 UNKNOWN Ilayaraja
Kindly help me to write a query.
I suspect this isn't what you're after but without an understanding of the problem this query should do what you ask:
select ID,
case when Stat = null then 'UNKNOWN'
WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
WHEN Stat = 'UNK' THEN 'UNKNOWN'
WHEN Stat = ' ' THEN 'UNKNOWN'
ELSE 'UNKNOWN'
END as unknown,
case
when charindex(',', rtrim(lbl)) = 0 then rtrim(lbl)
else left(lbl, charindex(',', rtrim(lbl)) - 1)
end as lbl
from #Ram
select ID,
case when Stat = null then 'UNKNOWN'
WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
WHEN Stat = 'UNK' THEN 'UNKNOWN'
WHEN Stat = ' ' THEN 'UNKNOWN'
ELSE 'UNKNOWN'
END as unknown,
case
when charindex(',', rtrim(lbl)) = 0 then rtrim(lbl)
else left(lbl, charindex(',', rtrim(lbl)) - 1)
end as lbl
from #Ram
SELECT ID,
case when Stat = null then 'UNKNOWN'
WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
WHEN Stat = 'UNK' THEN 'UNKNOWN'
WHEN Stat = ' ' THEN 'UNKNOWN'
ELSE 'UNKNOWN'
END,
CASE WHEN CHARINDEX( ',',LBL) = 0 then LBL
else SUBSTRING(LBL,0,CHARINDEX( ',',LBL))
end
From #Ram
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DECLARE @Ram TABLE (ID int, Stat Varchar(30), Lbl varchar(500))
INSERT INTO @Ram(ID,Stat, Lbl)
VALUES (1, 'null','Rahul,Southern'),
(2,'UNKNOWN','Ram,Rishi,Ta
(3, 'UNK', 'Dhoni'),
(4, ' ', 'Ilayaraja')
SELECT ID,
case when Stat = null then 'UNKNOWN'
WHEN Stat = 'UNKNOWN' THEN 'UNKNOWN'
WHEN Stat = 'UNK' THEN 'UNKNOWN'
WHEN Stat = ' ' THEN 'UNKNOWN'
ELSE 'UNKNOWN'
END,
CASE WHEN CHARINDEX(',',Lbl)=0 THEN Lbl ELSE LEFT(Lbl,CHARINDEX(',',Lbl
From @Ram